﻿using System;
using System.ComponentModel;
using System.Data;
using System.Web.Services;
using Shuba.Model;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;
using System.IO;
using System.DirectoryServices;
using Shuba.Framework;
using System.IO.Compression;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Web;

namespace Shuba.Web.ws
{
    /// <summary>
    /// Summary description for wsenh2b2
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class enh2 : System.Web.Services.WebService
    {
        static Encryption Encryption = new Encryption();
        static string SPADADBEncCode = Encryption.Decrypt(ConfigurationManager.AppSettings["enc.code.spada"]);
        static string SPADAAppCode = ConfigurationManager.AppSettings["app.code.spada"];

        string AppCode = ConfigurationManager.AppSettings["app.code.spada"];

        UserInfo GetLoginInfo(string nip, string AppCode)
        {
            UserInfo user = new UserInfo();
            Database db = DBConn.CreateDatabase();
            DbCommand cmd;

            string sql = @"EXEC GET_LOGIN_USER_INFO @NIP, @APP_ID";
            cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@NIP", DbType.String, nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    user = new UserInfo(dr.GetInt32(0), dr.GetString(1).Trim(), dr.GetString(2), dr.GetString(3), dr.GetString(4), dr.GetString(5), dr.GetString(6), dr.GetInt32(7), dr.GetString(8) == "1" ? true : false, dr.GetString(9) == "1" ? true : false, dr.GetDateTime(10), dr.GetString(11), dr.GetString(12));
                }
                dr.Close();
            }
            return user;
        }

        DataSet TransformIntoDataSet(byte[] value)
        {
            using (MemoryStream memStream = new MemoryStream(value))
            {
                using (GZipStream unzipStream = new GZipStream(memStream, CompressionMode.Decompress))
                {
                    DataSet objDataSet = new DataSet();
                    objDataSet.ReadXml(unzipStream, XmlReadMode.ReadSchema);
                    unzipStream.Close();
                    memStream.Close();
                    return objDataSet;
                }
            }
        }

        byte[] TransformIntoBytes(DataSet ds)
        {
            byte[] data;
            MemoryStream memStream = new MemoryStream();
            GZipStream zipStream = new GZipStream(memStream, CompressionMode.Compress);
            ds.WriteXml(zipStream, XmlWriteMode.WriteSchema);
            zipStream.Close();
            data = memStream.ToArray();
            memStream.Close();
            return data;
        }

        [WebMethod]
        public byte[] GetAlternate(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetAlternate_ENH2B3");

                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPipelineUM(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetPipelineUM_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public int GetCountPipeline(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetCountPipeline_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                object result = db.ExecuteScalar(cmd);

                if (result != null) return Convert.ToInt32(result);
            }
            return 0;
        }

        [WebMethod]
        public byte[] GetTopupUM(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetTopupUM_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public int GetCountTopup(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetCountTopup_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                object result = db.ExecuteScalar(cmd);

                if (result != null) return Convert.ToInt32(result);
            }
            return 0;
        }

        [WebMethod]
        public int GetCountRefferal(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetCountRefferal_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                object result = db.ExecuteScalar(cmd);

                if (result != null) return Convert.ToInt32(result);
            }
            return 0;
        }

        [WebMethod]
        public byte[] GetRefferalUM(string nip, string pass, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetRefferalUM_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetRefferalSO(string nip, string pass, string unit, string macAddress)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetRefferalSO_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, macAddress);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetProspectAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetProspectAll_ENH2B3");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPVisitAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetPVisitAll_ENH2B3");

                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPAddressAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetPAddressAll_ENH2B3");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPBankAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetPBankAll_ENH2B3");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPDocumentAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetPDocumentAll_ENH2B3");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetPEmgContactAll(string nip, string pass, string AoCode, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetPEmgContactAll_ENH2B3");
                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AoCode);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                return TransformIntoBytes(db.ExecuteDataSet(cmd));
            }
            return null;
        }

        [WebMethod]
        public byte[] GetProspectUpdate(string nip, string pass, string mac_address, string unit)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetProspectUpdate_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, mac_address);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public string RequestDedup(string nip, string pass, byte[] data)
        {
            if (IsValidUser(nip, pass))
            {
                DataSet MyDataSet = TransformIntoDataSet(data);
                if (ValidateDataSetEmpty(MyDataSet))
                {
                    Database db = DBConn.CreateDatabase();
                    DataRow dr = MyDataSet.Tables[0].Rows[0];

                    DbCommand cmd = db.GetSqlStringCommand(@"SELECT COUNT(0) FROM TBL_DEDUP_CHECKING_REQ
                    WHERE PROCESS_STATE = '1' AND PROSPECT_ID = @PROSPECT_ID");

                    db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));

                    object result = db.ExecuteScalar(cmd);
                    if (Convert.ToInt32(result) > 0) return "1";

                    cmd = db.GetSqlStringCommand(@"
                        
                    UPDATE [TBL_DEDUP_CHECKING_REQ_TMP]
                     SET  
                           [PROSPECT_NO] = @PROSPECT_NO
                          ,[UNIT_CODE] = @UNIT_CODE
                          ,[KTP_NAME] = @KTP_NAME
                          ,[PLACE_OF_BIRTH] = @PLACE_OF_BIRTH
                          ,[DATE_OF_BIRTH] = @DATE_OF_BIRTH
                          ,[KTP_NO] = @KTP_NO
                          ,[ADDRESS_BUSINESS] = @ADDRESS_BUSINESS
                          ,[MOBILE_PHONE] = @MOBILE_PHONE
                          ,[RESIDENTIAL_PHONE] = @RESIDENTIAL_PHONE
                          ,[OFFICE_PHONE] = @OFFICE_PHONE
                          ,[PCD_DATE] = @PCD_DATE
                          ,[NO_OF_REQUEST] = @NO_OF_REQUEST + 1
                     WHERE [PROSPECT_ID] = @PROSPECT_ID 

                     IF @@ROWCOUNT = 0

                     INSERT INTO [TBL_DEDUP_CHECKING_REQ_TMP]
                               ([PROSPECT_ID],[PROSPECT_NO],[UNIT_CODE],[KTP_NAME]
                               ,[PLACE_OF_BIRTH],[DATE_OF_BIRTH]
                               ,[KTP_NO],[ADDRESS_BUSINESS]
                               ,[MOBILE_PHONE],[RESIDENTIAL_PHONE]
                               ,[OFFICE_PHONE],[PCD_DATE]
                               ,[NO_OF_REQUEST])
                         VALUES
                               (@PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@KTP_NAME
                               ,@PLACE_OF_BIRTH,@DATE_OF_BIRTH
                               ,@KTP_NO,@ADDRESS_BUSINESS
                               ,@MOBILE_PHONE,@RESIDENTIAL_PHONE
                               ,@OFFICE_PHONE,@PCD_DATE
                               ,@NO_OF_REQUEST + 1)");
                    db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                    db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                    db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                    db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                    db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                    db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                    db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                    db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                    db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                    db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                    db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                    db.AddInParameter(cmd, "@PCD_DATE", DbType.DateTime, Convert.ToDateTime(dr["PCD_DATE"]));
                    db.AddInParameter(cmd, "@NO_OF_REQUEST", DbType.Byte, Convert.ToByte(dr["NO_OF_CALLS"]));
                    db.ExecuteNonQuery(cmd);
                    return "0";
                }
            }
            return "~";
        }

        [WebMethod]
        public byte[] GetDedup(string nip, string pass, string prn_original)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetDedup_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prn_original);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetDedupByUnit(string nip, string pass, string unit, string mac_address, DateTime update_date)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_GetDedupByUnit_ENH2B3");

                cmd.CommandTimeout = 600;
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, unit);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, mac_address);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, update_date);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public int DelProspectUpdate(byte[] data, string nip, string pass)
        {
            int result = 0;
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                {
                    DbCommand cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID=@PROSPECT_ID");

                    db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                    if (db.ExecuteNonQuery(cmd) > 0) result++;
                }
            }
            return result;
        }

        [WebMethod]
        public DateTime GetProcessingDate()
        {
            DateTime processDate = DateTime.Now;
            Database db = DBConn.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC");
            object result = db.ExecuteScalar(cmd);

            if (result != null)
            {
                processDate = Convert.ToDateTime(result);
            }
            else processDate = DateTime.Now;
            return processDate;
        }

        private int GUFUP()
        {
            int gufup = 0;
            Database db = DBConn.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT TOP 1 GU_FUP FROM TBL_SYS_PARAMS (NOLOCK) ORDER BY UPDATE_DATE DESC");
            object result = db.ExecuteScalar(cmd);

            if (result != null)
            {
                gufup = Convert.ToInt32(result);
            }
            else gufup = 7;
            return gufup;
        }

        private string JENIS_USAHA(string JenisUsaha)
        {
            string ju;
            Database db = DBConn.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT MS_STD_ID FROM TBL_MS_STANDARD (NOLOCK) WHERE MS_STD_NAME = @MS_STD_NAME AND MS_STD_GRP_ID = 14 ORDER BY MS_STD_ID");
            db.AddInParameter(cmd, "@MS_STD_NAME", DbType.String, JenisUsaha);

            object result = db.ExecuteScalar(cmd);

            if (result != null) ju = Convert.ToString(result);
            else ju = "";

            return ju;
        }

        private string SOURCE_CODE(string BRANCH_ID)
        {
            string branch;
            Database db = DBConn.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT SOURCE_CODE FROM TBL_MS_BRANCH (NOLOCK) WHERE BRANCH_ID = @BRANCH_ID");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);

            object result = db.ExecuteScalar(cmd);

            if (result != null) branch = Convert.ToString(result);
            else branch = "";

            return branch;
        }

        [WebMethod]
        public int UpdatePipelineAssignedTo(DateTime AssignedDate, string ROW_ID, string AO_CODE, string LoginUser, string LoginPass)
        {
            int l_result = 0;
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();
                DbConnection connection = db.CreateConnection();
                connection.Open();
                DbTransaction dbTran = connection.BeginTransaction();

                try
                {
                    DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_PIPELINE SET IS_ASSIGNED = '1', IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE, ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE ROW_ID=@ROW_ID");

                    db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);
                    db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                    db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, AssignedDate);
                    db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, LoginUser);
                    db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, AssignedDate);

                    if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                    {
                        cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PIPELINE_DOWNLOAD WHERE ROW_ID=@ROW_ID");
                        db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);
                        db.ExecuteNonQuery(cmd, dbTran);

                        cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_PIPELINE (NOLOCK) WHERE ROW_ID=@ROW_ID");
                        db.AddInParameter(cmd, "@ROW_ID", DbType.String, ROW_ID);

                        DataSet ds = db.ExecuteDataSet(cmd, dbTran);
                        if (ValidateDataSetEmpty(ds))
                        {
                            DataRow dr = ds.Tables[0].Rows[0];
                            int guFup = GUFUP();
                            DateTime processTime = GetProcessingDate();
                            string AssignedTo = dr["ASSIGNED_TO"].ToString().Trim().Length < 5 ? dr["ASSIGNED_TO"].ToString().Trim().PadRight(5, 'X') : dr["ASSIGNED_TO"].ToString().Trim();
                            string SourceCode = SOURCE_CODE(Convert.ToString(dr["UNIT_CODE"]));
                            DateTime dateFollowUp = AddWorkDays(processTime, guFup, GetHoliday());
                            string jenisUsaha = JENIS_USAHA(dr["JENIS_USAHA"].ToString());

                            string address = dr["ADDRESS_BUSINESS"].ToString().Trim();
                            string prospect_id = dr["UNIT_CODE"].ToString().Trim() + AssignedTo + ProcessTime(processTime).ToString("yyMMddHHmmss");
                            if (prospect_id.Trim().Length < 21) prospect_id = prospect_id.Trim().PadRight(21, 'X');
                            if (prospect_id.Trim().Length > 21) prospect_id = prospect_id.Trim().Substring(0, 21);
                            if (address.Trim().Length > 255) address = address.Substring(0, 255);

                            ProspectInfo prospect = new ProspectInfo
                            {
                                PROSPECT_ID = prospect_id.Trim().ToUpper(),
                                PROSPECT_NO = "n/a",
                                AO_CODE = dr["ASSIGNED_TO"].ToString(),
                                UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                SOURCE_CODE = SourceCode,
                                JENIS_USAHA = jenisUsaha,
                                FULL_NAME = dr["FULL_NAME"].ToString(),
                                RENCANA_PLAFON = dr["RENCANA_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["RENCANA_PLAFON"].ToString()),
                                MOBILE_PHONE = dr["MOBILE_PHONE"].ToString(),
                                OFFICE_PHONE = dr["OFFICE_PHONE"].ToString(),
                                RESIDENTIAL_PHONE = dr["RESIDENTIAL_PHONE"].ToString(),
                                KODYA_KAB_BUSINESS = dr["KODYA_KAB_BUSINESS"].ToString(),
                                KECAMATAN_BUSINESS = dr["KECAMATAN_BUSINESS"].ToString(),
                                KELURAHAN_BUSINESS = dr["KELURAHAN_BUSINESS"].ToString(),
                                ADDRESS_BUSINESS = address,
                                ZIP_CODE_BUSINESS = dr["ZIP_CODE_BUSINESS"].ToString(),
                                PROGRESS_STATUS = "NEW",
                                PROSPECT_TYPE = dr["PROSPECT_TYPE"].ToString(),
                                DEBTOR_TYPE = dr["DEBTOR_TYPE"].ToString(),
                                CHANNELING = dr["CHANNELING"].ToString(),
                                ENTRY_BY = LoginUser,
                                ENTRY_DATE = AssignedDate,
                                GU_FUP = dateFollowUp,
                                IS_DEDUP = "0",
                                OMZET_1 = dr["OMZET_1"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_1"].ToString()),
                                OMZET_2 = dr["OMZET_2"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_2"].ToString()),
                                OMZET_3 = dr["OMZET_3"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_3"].ToString()),
                                OMZET_4 = dr["OMZET_4"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_4"].ToString()),
                                OMZET_5 = dr["OMZET_5"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_5"].ToString()),
                                OMZET_6 = dr["OMZET_6"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_6"].ToString()),
                                OMZET_7 = dr["OMZET_7"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_7"].ToString()),
                                OMZET_8 = dr["OMZET_8"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_8"].ToString()),
                                OMZET_9 = dr["OMZET_9"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_9"].ToString()),
                                OMZET_10 = dr["OMZET_10"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_10"].ToString()),
                                OMZET_11 = dr["OMZET_11"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_11"].ToString()),
                                OMZET_12 = dr["OMZET_12"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_12"].ToString()),
                                OMZET_AVERAGE = dr["OMZET_AVERAGE"].ToString() == string.Empty ? 0 : float.Parse(dr["OMZET_AVERAGE"].ToString()),
                                EKSTERNAL = dr["EKSTERNAL"].ToString(),
                                REFERENSI = dr["REFERENSI"].ToString(),
                                ALTERNATE_FOR = "",
                                FLAG_FREEZE = dr["FLAG_FREEZE"].ToString() == string.Empty ? Convert.ToInt16(0) : Convert.ToInt16(dr["FLAG_FREEZE"])
                            };

                            cmd = db.GetSqlStringCommand(@"INSERT INTO TBL_PROSPECT 
                                (PROSPECT_ID, PROSPECT_NO, UNIT_CODE, SOURCE_CODE, AO_CODE, 
                                 FULL_NAME, JENIS_USAHA, PROGRESS_STATUS, RENCANA_PLAFON, MOBILE_PHONE, 
                                 OFFICE_PHONE, RESIDENTIAL_PHONE, KODYA_KAB_BUSINESS, KECAMATAN_BUSINESS, KELURAHAN_BUSINESS, 
                                 ADDRESS_BUSINESS, ZIP_CODE_BUSINESS, IS_DOCUMENT_COMPLETED, ENTRY_BY, ENTRY_DATE, 
                                 UPDATE_BY, UPDATE_DATE, PIPELINE_START_TIME, GU_FUP, IS_DEDUP,
                                 PROSPECT_TYPE, DEBTOR_TYPE, CHANNELING,
                                 OMZET_1, OMZET_2, OMZET_3, OMZET_4, OMZET_5,
                                 OMZET_6, OMZET_7, OMZET_8, OMZET_9, OMZET_10,
                                 OMZET_11, OMZET_12, OMZET_AVERAGE, IS_PIPELINE_KP, EKSTERNAL, REFERENSI, NIP, ALTERNATE_FOR, KARAKTER, FLAG_FREEZE) 
                            VALUES (@PROSPECT_ID, @PROSPECT_NO, @UNIT_CODE, @SOURCE_CODE, @AO_CODE, 
                                 @FULL_NAME, @JENIS_USAHA, @PROGRESS_STATUS, @RENCANA_PLAFON, @MOBILE_PHONE, 
                                 @OFFICE_PHONE, @RESIDENTIAL_PHONE, @KODYA_KAB_BUSINESS, @KECAMATAN_BUSINESS, @KELURAHAN_BUSINESS, 
                                 @ADDRESS_BUSINESS, @ZIP_CODE_BUSINESS, @IS_DOCUMENT_COMPLETED, @ENTRY_BY, @ENTRY_DATE, 
                                 @UPDATE_BY, @UPDATE_DATE, @PIPELINE_START_TIME, @GU_FUP, @IS_DEDUP,
                                 @PROSPECT_TYPE, @DEBTOR_TYPE, @CHANNELING,
                                 @OMZET_1, @OMZET_2, @OMZET_3, @OMZET_4, @OMZET_5,
                                 @OMZET_6, @OMZET_7, @OMZET_8, @OMZET_9, @OMZET_10,
                                 @OMZET_11, @OMZET_12, @OMZET_AVERAGE, @IS_PIPELINE_KP, @EKSTERNAL, @REFERENSI, @NIP, @ALTERNATE_FOR, @KARAKTER, @FLAG_FREEZE)");

                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID.Trim().ToUpper());
                            db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, prospect.PROSPECT_NO);
                            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, prospect.UNIT_CODE);
                            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, prospect.SOURCE_CODE);
                            db.AddInParameter(cmd, "@AO_CODE", DbType.String, prospect.AO_CODE);
                            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, prospect.FULL_NAME);
                            db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, prospect.JENIS_USAHA);
                            db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, prospect.PROGRESS_STATUS);
                            db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, prospect.RENCANA_PLAFON);
                            db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, prospect.MOBILE_PHONE);
                            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, prospect.OFFICE_PHONE);
                            db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, prospect.RESIDENTIAL_PHONE);
                            db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, prospect.KODYA_KAB_BUSINESS);
                            db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, prospect.KECAMATAN_BUSINESS);
                            db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, prospect.KELURAHAN_BUSINESS);
                            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, prospect.ADDRESS_BUSINESS);
                            db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, prospect.ZIP_CODE_BUSINESS);
                            db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, "0");
                            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, prospect.GU_FUP);
                            db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, prospect.IS_DEDUP);
                            db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospect.PROSPECT_TYPE);
                            db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, prospect.DEBTOR_TYPE);
                            db.AddInParameter(cmd, "@CHANNELING", DbType.String, prospect.CHANNELING);
                            db.AddInParameter(cmd, "@OMZET_1", DbType.Double, prospect.OMZET_1);
                            db.AddInParameter(cmd, "@OMZET_2", DbType.Double, prospect.OMZET_2);
                            db.AddInParameter(cmd, "@OMZET_3", DbType.Double, prospect.OMZET_3);
                            db.AddInParameter(cmd, "@OMZET_4", DbType.Double, prospect.OMZET_4);
                            db.AddInParameter(cmd, "@OMZET_5", DbType.Double, prospect.OMZET_5);
                            db.AddInParameter(cmd, "@OMZET_6", DbType.Double, prospect.OMZET_6);
                            db.AddInParameter(cmd, "@OMZET_7", DbType.Double, prospect.OMZET_7);
                            db.AddInParameter(cmd, "@OMZET_8", DbType.Double, prospect.OMZET_8);
                            db.AddInParameter(cmd, "@OMZET_9", DbType.Double, prospect.OMZET_9);
                            db.AddInParameter(cmd, "@OMZET_10", DbType.Double, prospect.OMZET_10);
                            db.AddInParameter(cmd, "@OMZET_11", DbType.Double, prospect.OMZET_11);
                            db.AddInParameter(cmd, "@OMZET_12", DbType.Double, prospect.OMZET_12);
                            db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, prospect.OMZET_AVERAGE);
                            db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, "1");
                            db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, prospect.EKSTERNAL);
                            db.AddInParameter(cmd, "@REFERENSI", DbType.String, prospect.REFERENSI);
                            db.AddInParameter(cmd, "@NIP", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, prospect.ALTERNATE_FOR);
                            db.AddInParameter(cmd, "@KARAKTER", DbType.String, "3");
                            db.AddInParameter(cmd, "FLAG_FREEZE", DbType.Int16, prospect.FLAG_FREEZE);

                            if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                            {
                                cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID; INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");
                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID);
                                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, prospect.ENTRY_DATE);

                                l_result = db.ExecuteNonQuery(cmd, dbTran);
                            }
                        }
                    }
                    if (l_result > 0) dbTran.Commit();
                    else dbTran.Rollback();
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UpdatePipelineAssignedTo : " + ex.Message.ToString(), true);
                    dbTran.Rollback();
                }
                finally
                {
                    if (connection.State == ConnectionState.Open) connection.Close();
                }
            }
            return l_result;
        }

        [WebMethod]
        public int UpdateTopupAssignedTo(DateTime AssignedDate, string AO_CODE, string CIF_NUMBER, string LOAN_NUMBER, string LOGIN_USER, string LOGIN_PASSWD)
        {
            int l_result = 0;
            if (IsValidUser(LOGIN_USER, LOGIN_PASSWD))
            {
                Database db = DBConn.CreateDatabase();
                DbConnection connection = db.CreateConnection();
                connection.Open();
                DbTransaction dbTran = connection.BeginTransaction();

                try
                {
                    DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_TOPUP SET IS_ASSIGNED = '1', IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE, ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");

                    db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                    db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                    db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                    db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, AssignedDate);
                    db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, LOGIN_USER);
                    db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, AssignedDate);

                    if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                    {
                        cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_TOPUP_DOWNLOAD WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);
                        db.ExecuteNonQuery(cmd, dbTran);

                        cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_TOPUP (NOLOCK) WHERE CIF_NUMBER=@CIF_NUMBER AND LOAN_NUMBER=@LOAN_NUMBER");
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.String, CIF_NUMBER);
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, LOAN_NUMBER);

                        DataSet ds = db.ExecuteDataSet(cmd, dbTran);
                        if (ValidateDataSetEmpty(ds))
                        {
                            DataRow dr = ds.Tables[0].Rows[0];

                            int guFup = GUFUP();
                            DateTime processTime = GetProcessingDate();
                            string AssignedTo = dr["ASSIGNED_TO"].ToString().Trim().Length < 5 ? dr["ASSIGNED_TO"].ToString().Trim().PadRight(5, 'X') : dr["ASSIGNED_TO"].ToString().Trim();
                            string SourceCode = SOURCE_CODE(Convert.ToString(dr["UNIT_CODE"]));
                            DateTime dateFollowUp = AddWorkDays(processTime, guFup, GetHoliday());
                            string jenisUsaha = JENIS_USAHA(dr["JENIS_USAHA"].ToString());

                            string address = dr["ADDRESS_BUSINESS"].ToString().Trim();
                            string prospect_id = dr["UNIT_CODE"].ToString().Trim() + AssignedTo + ProcessTime(processTime).ToString("yyMMddHHmmss");
                            if (prospect_id.Trim().Length < 21) prospect_id = prospect_id.Trim().PadRight(21, 'X');
                            if (prospect_id.Trim().Length > 21) prospect_id = prospect_id.Trim().Substring(0, 21);
                            if (address.Trim().Length > 255) address = address.Substring(0, 255);

                            ProspectInfo prospect = new ProspectInfo
                            {
                                PROSPECT_ID = prospect_id.Trim().ToUpper(),
                                PROSPECT_NO = "n/a",
                                AO_CODE = dr["ASSIGNED_TO"].ToString().Trim(),
                                UNIT_CODE = dr["UNIT_CODE"].ToString(),
                                SOURCE_CODE = SourceCode,
                                FULL_NAME = dr["DEBITOR_NAME"].ToString(),
                                CIF_NUMBER = dr["CIF_NUMBER"].ToString() == string.Empty ? 0 : int.Parse(dr["CIF_NUMBER"].ToString()),
                                LOAN_NUMBER = dr["LOAN_NUMBER"].ToString(),
                                PRODUCT_CODE = dr["PRODUCT_CODE"].ToString(),
                                CURRENT_OS = dr["CURRENT_OS"].ToString() == string.Empty ? 0 : float.Parse(dr["CURRENT_OS"].ToString()),
                                CURRENT_INSTALLMENT = dr["CURRENT_INSTALLMENT"].ToString() == string.Empty ? 0 : float.Parse(dr["CURRENT_INSTALLMENT"].ToString()),
                                OFFER_PLAFON = dr["OFFER_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_PLAFON"].ToString()),
                                RENCANA_PLAFON = dr["OFFER_PLAFON"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_PLAFON"].ToString()),
                                OFFER_INSTALLMENT = dr["OFFER_INSTALLMENT"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_INSTALLMENT"].ToString()),
                                OFFER_INTEREST = dr["OFFER_INTEREST"].ToString(),
                                OFFER_TENOR = dr["OFFER_TENOR"].ToString() == string.Empty ? Convert.ToInt16(0) : Int16.Parse(dr["OFFER_TENOR"].ToString()),
                                OFFER_FRESH_MONEY = dr["OFFER_FRESH_MONEY"].ToString() == string.Empty ? 0 : float.Parse(dr["OFFER_FRESH_MONEY"].ToString()),
                                PRIORITY = dr["PRIORITY"].ToString(),
                                JENIS_USAHA = jenisUsaha,
                                KODYA_KAB_BUSINESS = dr["KODYA_KAB_BUSINESS"].ToString(),
                                KECAMATAN_BUSINESS = dr["KECAMATAN_BUSINESS"].ToString(),
                                KELURAHAN_BUSINESS = dr["KELURAHAN_BUSINESS"].ToString(),
                                ZIP_CODE_BUSINESS = dr["ZIP_CODE_BUSINESS"].ToString(),
                                ADDRESS_BUSINESS = address,
                                MOBILE_PHONE = dr["MOBILE_PHONE"].ToString(),
                                OFFICE_PHONE = dr["OFFICE_PHONE"].ToString(),
                                RESIDENTIAL_PHONE = dr["RESIDENTIAL_PHONE"].ToString(),
                                PROGRESS_STATUS = "NEW",
                                ENTRY_BY = LOGIN_USER,
                                ENTRY_DATE = AssignedDate,
                                GU_FUP = dateFollowUp,
                                PROSPECT_TYPE = dr["PROSPECT_TYPE"].ToString(),
                                DEBTOR_TYPE = dr["DEBTOR_TYPE"].ToString(),
                                CHANNELING = dr["CHANNELING"].ToString(),
                                EKSTERNAL = dr["EKSTERNAL"].ToString(),
                                REFERENSI = dr["REFERENSI"].ToString(),
                                ALTERNATE_FOR = "",
                                FLAG_FREEZE = dr["FLAG_FREEZE"].ToString() == string.Empty ? Convert.ToInt16(0) : Convert.ToInt16(dr["FLAG_FREEZE"])
                            };

                            cmd = db.GetSqlStringCommand(@"INSERT INTO TBL_PROSPECT ([PROSPECT_ID], [PROSPECT_NO], [UNIT_CODE], [SOURCE_CODE], [AO_CODE], [PROGRESS_STATUS], [CIF_NUMBER], 
                                                        [LOAN_NUMBER], [FULL_NAME], [PRODUCT_CODE], [CURRENT_OS], 
                                                        [CURRENT_INSTALLMENT], [RENCANA_PLAFON], [OFFER_PLAFON],
                                                        [OFFER_INSTALLMENT], [OFFER_INTEREST], [OFFER_TENOR], 
                                                        [OFFER_FRESH_MONEY], [PRIORITY], [KODYA_KAB_BUSINESS],
                                                        [KECAMATAN_BUSINESS], [KELURAHAN_BUSINESS], [ZIP_CODE_BUSINESS],
                                                        [ADDRESS_BUSINESS], [MOBILE_PHONE], [OFFICE_PHONE], [RESIDENTIAL_PHONE], [JENIS_USAHA],
                                                        [ENTRY_BY], [ENTRY_DATE], [UPDATE_BY], [UPDATE_DATE], [PIPELINE_START_TIME], [GU_FUP],
                                                        [PROSPECT_TYPE], [DEBTOR_TYPE], [CHANNELING], [EKSTERNAL], [REFERENSI], [NIP], [ALTERNATE_FOR], [KARAKTER], [FLAG_FREEZE])
                                                      VALUES (@PROSPECT_ID, @PROSPECT_NO, @UNIT_CODE, @SOURCE_CODE, @AO_CODE, @PROGRESS_STATUS, @CIF_NUMBER, 
                                                        @LOAN_NUMBER, @FULL_NAME, @PRODUCT_CODE, @CURRENT_OS, 
                                                        @CURRENT_INSTALLMENT, @RENCANA_PLAFON, @OFFER_PLAFON,
                                                        @OFFER_INSTALLMENT, @OFFER_INTEREST, @OFFER_TENOR, 
                                                        @OFFER_FRESH_MONEY, @PRIORITY, @KODYA_KAB_BUSINESS,
                                                        @KECAMATAN_BUSINESS, @KELURAHAN_BUSINESS, @ZIP_CODE_BUSINESS,
                                                        @ADDRESS_BUSINESS, @MOBILE_PHONE, @OFFICE_PHONE, @RESIDENTIAL_PHONE, @JENIS_USAHA,
                                                        @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @PIPELINE_START_TIME, @GU_FUP,
                                                        @PROSPECT_TYPE, @DEBTOR_TYPE, @CHANNELING, @EKSTERNAL, @REFERENSI, @NIP, @ALTERNATE_FOR, @KARAKTER, @FLAG_FREEZE)");
                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID.Trim().ToUpper());
                            db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, prospect.PROSPECT_NO);
                            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, prospect.UNIT_CODE);
                            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, prospect.SOURCE_CODE);
                            db.AddInParameter(cmd, "@AO_CODE", DbType.String, prospect.AO_CODE);
                            db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, prospect.PROGRESS_STATUS);
                            db.AddInParameter(cmd, "@FULL_NAME", DbType.String, prospect.FULL_NAME);
                            db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, prospect.CIF_NUMBER);
                            db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, prospect.LOAN_NUMBER);
                            db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, prospect.PRODUCT_CODE);
                            db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, prospect.CURRENT_OS);
                            db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, prospect.CURRENT_INSTALLMENT);
                            db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, prospect.RENCANA_PLAFON);
                            db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, prospect.OFFER_PLAFON);
                            db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, prospect.OFFER_INSTALLMENT);
                            db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, prospect.OFFER_INTEREST);
                            db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, prospect.OFFER_TENOR);
                            db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, prospect.OFFER_FRESH_MONEY);
                            db.AddInParameter(cmd, "@PRIORITY", DbType.String, prospect.PRIORITY);
                            db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, prospect.KODYA_KAB_BUSINESS);
                            db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, prospect.KECAMATAN_BUSINESS);
                            db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, prospect.KELURAHAN_BUSINESS);
                            db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, prospect.ZIP_CODE_BUSINESS);
                            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, prospect.ADDRESS_BUSINESS);
                            db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, prospect.MOBILE_PHONE);
                            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, prospect.OFFICE_PHONE);
                            db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, prospect.RESIDENTIAL_PHONE);
                            db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, prospect.JENIS_USAHA);
                            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, prospect.ENTRY_DATE);
                            db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, prospect.GU_FUP);
                            db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, prospect.PROSPECT_TYPE);
                            db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, prospect.DEBTOR_TYPE);
                            db.AddInParameter(cmd, "@CHANNELING", DbType.String, prospect.CHANNELING);
                            db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, prospect.EKSTERNAL);
                            db.AddInParameter(cmd, "@REFERENSI", DbType.String, prospect.REFERENSI);
                            db.AddInParameter(cmd, "@NIP", DbType.String, prospect.ENTRY_BY);
                            db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, prospect.ALTERNATE_FOR);
                            db.AddInParameter(cmd, "@KARAKTER", DbType.String, "2");
                            db.AddInParameter(cmd, "@FLAG_FREEZE", DbType.Int16, prospect.FLAG_FREEZE);

                            if (db.ExecuteNonQuery(cmd, dbTran) > 0)
                            {
                                cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID; INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");
                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, prospect.PROSPECT_ID);
                                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.String, prospect.ENTRY_DATE);

                                l_result = db.ExecuteNonQuery(cmd, dbTran);
                            }
                        }
                    }
                    if (l_result > 0) dbTran.Commit();
                    else dbTran.Rollback();
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UpdateTopupAssignedTo : " + ex.Message.ToString(), true);
                    dbTran.Rollback();
                }
                finally
                {
                    if (connection.State == ConnectionState.Open) connection.Close();
                }
            }
            return l_result;
        }

        DateTime AddWorkDays(DateTime date, int workingDays, List<DateTime> Holidays)
        {
            int direction = workingDays < 0 ? -1 : 1;
            DateTime newDate = date.Date;
            while (workingDays != 0)
            {
                newDate = newDate.AddDays(direction);
                if (newDate.DayOfWeek != DayOfWeek.Saturday &&
                    newDate.DayOfWeek != DayOfWeek.Sunday &&
                    !Holidays.Contains(newDate))
                {
                    workingDays -= direction;
                }
            }
            return newDate;
        }

        List<DateTime> GetHoliday()
        {
            List<DateTime> holiday = new List<DateTime>();
            try
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(@"SELECT HOLIDAY_DATE FROM TBL_MS_HOLIDAY (NOLOCK) WHERE YEAR(HOLIDAY_DATE) = YEAR(GETDATE()) ORDER BY HOLIDAY_DATE");
                DataSet dt = db.ExecuteDataSet(cmd);

                if (ValidateDataSetEmpty(dt))
                {
                    foreach (DataRow item in dt.Tables[0].Rows)
                    {
                        holiday.Add(Convert.ToDateTime(item[0]));
                    }
                }
            }
            catch (Exception ex)
            {
                CreateErrorLog("enh2 GetHoliday : " + ex.Message.ToString(), true);
            }
            return holiday;
        }

        bool ValidateDataSetEmpty(DataSet vDataSet)
        {
            if (vDataSet != null)
            {
                if (vDataSet.Tables[0].Rows.Count > 0)
                {
                    return true;
                }
            }
            return false;
        }

        DateTime ProcessTime(DateTime ProcesingDate)
        {
            return new DateTime(
                ProcesingDate.Year,
                ProcesingDate.Month,
                ProcesingDate.Day,
                DateTime.Now.Hour,
                DateTime.Now.Minute,
                DateTime.Now.Second,
                DateTime.Now.Millisecond,
                ProcesingDate.Kind);
        }

        [WebMethod]
        public string IsLogin(string NIP, string IPAddress)
        {
            string status = "";
            long LoginPeriod;

            Shuba.Business.AppParam appParam = new Shuba.Business.AppParam();
            UserInfo info = GetLoginInfo(NIP, AppCode);
            DateTime LastLoginDate = info.LoginDate;
            LoginPeriod = appParam.GetDateDb().Subtract(LastLoginDate).Minutes;

            AppParamInfo appParamInfo = appParam.Get();
            Int16 timeOut = appParamInfo.TIME_OUT;
            Int16 refreshTime = appParamInfo.REFRESH_TIME;

            if ((info.IsLogin == false) || (info.AppId == SPADAAppCode && info.IsLogin == true && IPAddress == info.LoginAddr && LoginPeriod >= refreshTime) || (info.IsLogin == true && IPAddress != info.LoginAddr && LoginPeriod >= timeOut))
            {
                status = "";
            }
            else
            {
                status = String.Format("Tidak bisa Login, user ini sudah Login atau Logout dengan tidak benar\nLogin terakhir dari {0} pada {1:dd MMM yyyy hh:mm:ss tt}", NIP, LastLoginDate);
            }
            return status;
        }

        [WebMethod]
        public int UpdateIsMigrationUpload(string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK)

	            UPDATE TBL_MIGRATION SET
		            IS_MIGRATION_UPLOAD = '1', 
		            MIGRATION_UPLOAD_DATE = @PROCESS_DATE
	            WHERE AO_CODE=@AO_CODE;
	            IF @@ROWCOUNT = 0
                INSERT INTO TBL_MIGRATION(AO_CODE, IS_MIGRATION_UPLOAD, MIGRATION_UPLOAD_DATE) 
                VALUES(@AO_CODE, '1', @PROCESS_DATE)");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public int UpdateIsMigrationDownload(string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @PROCESS_DATE DATETIME
                SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS (NOLOCK)

	            UPDATE TBL_MIGRATION SET
		            IS_MIGRATION_DOWNLOAD = '1', 
		            MIGRATION_DOWNLOAD_DATE = @PROCESS_DATE
	            WHERE (AO_CODE=@AO_CODE);
	            IF @@ROWCOUNT = 0
                BEGIN    
                    INSERT INTO TBL_MIGRATION(AO_CODE, IS_MIGRATION_DOWNLOAD, MIGRATION_DOWNLOAD_DATE) 
                    VALUES(@AO_CODE, '1', @PROCESS_DATE);
                END");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public int UpdateIsMigration(string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@" IF EXISTS (SELECT * FROM TBL_MIGRATION (NOLOCK) 
                WHERE IS_MIGRATION_DOWNLOAD = '1' 
                      AND IS_MIGRATION_UPLOAD = '1' 
                      AND AO_CODE = @AO_CODE)
                BEGIN 
                    UPDATE TBL_MIGRATION SET IS_MIGRATION_DOWNLOAD = '0', IS_MIGRATION_UPLOAD = '0' 
                    WHERE AO_CODE = @AO_CODE
                END");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public int UpdateMACAddress(string BRANCH_ID, string NIP, string MAC_ADDRESS, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"DECLARE @OLD_MAC_ADDRESS VARCHAR(50) 

                            SELECT @OLD_MAC_ADDRESS = MAC_ADDRESS 
                            FROM TBL_MS_BRANCH_NIP_SO (NOLOCK) WHERE BRANCH_ID = @BRANCH_ID AND NIP = @NIP

            	            UPDATE TBL_MS_BRANCH_NIP_SO SET
            		            MAC_ADDRESS = @MAC_ADDRESS, OLD_MAC_ADDRESS = @OLD_MAC_ADDRESS, IS_MIGRATION = '0'
                            WHERE BRANCH_ID = @BRANCH_ID AND NIP = @NIP AND IS_MIGRATION = '1';

                            UPDATE TBL_MS_BRANCH_NIP_SO SET
            		            MAC_ADDRESS = @MAC_ADDRESS
                            WHERE BRANCH_ID = @BRANCH_ID AND NIP = @NIP AND IS_MIGRATION = '0' AND MAC_ADDRESS = '' AND OLD_MAC_ADDRESS = '';");

                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
                db.AddInParameter(cmd, "@NIP", DbType.String, NIP);
                db.AddInParameter(cmd, "@MAC_ADDRESS", DbType.String, MAC_ADDRESS);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        bool IsValidUser(string LDAPUser, string LDAPPass)
        {
            return true;
        }

        bool IsLDAPUser(string LDAPUser, string LDAPPass)
        {
            if (basePage.ByPassAD())
                return true; //untuk testing;

            string LDAPServer = ConfigurationManager.AppSettings["ldap"];

            DirectoryEntry entry = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass);
            try
            {
                string EName = entry.Name; //Untuk Trap Error (LDAP tidak dapat) atau berhasil

                return true;
            }
            catch
            {
                return false;
            }
        }

        long CheckExpired(string LDAPUser, string LDAPPass)
        {
            long daysLeft = 0;
            try
            {
                long lastChanged = 0, maxDays = 0, maxPwdAge = 0;
                string LDAPServer = ConfigurationManager.AppSettings["ldap"];

                DirectoryEntry root = new DirectoryEntry(LDAPServer, LDAPUser, LDAPPass, AuthenticationTypes.Secure);
                DirectorySearcher searcher = new DirectorySearcher(root);

                searcher.Filter = "(&(objectClass=person)(SAMAccountName=" + LDAPUser + "))";
                searcher.PropertiesToLoad.Add("pwdlastset");

                SearchResult results = searcher.FindOne();

                if (results.Properties["pwdlastset"].Count > 0) lastChanged = (long)results.Properties["pwdlastset"][0];

                searcher = new DirectorySearcher(root);
                searcher.Filter = "maxPwdAge=*";
                searcher.PropertiesToLoad.Add("maxPwdAge");
                results = searcher.FindOne();

                if (results.Properties["maxPwdAge"].Count > 0) maxPwdAge = (Int64)results.Properties["maxPwdAge"][0];
                maxDays = maxPwdAge / -864000000000;

                daysLeft = maxDays - DateTime.Today.Subtract(Convert.ToDateTime(DateTime.FromFileTime((long)lastChanged).ToShortDateString())).Days;
            }
            catch (Exception ex)
            {
                CreateErrorLog("enh2 CheckExpired : " + ex.Message.ToString(), true);
            }
            return daysLeft;
        }

        [WebMethod]
        public string ResetPassword(string NIP, string oldPass, string newPass)
        {
            string res = "";
            string LDAPServer = ConfigurationManager.AppSettings["ldap"];
            DirectoryEntry uEntry = new DirectoryEntry(LDAPServer, NIP, oldPass, AuthenticationTypes.Secure);

            try
            {
                DirectoryEntry de = uEntry;
                DirectorySearcher search = new DirectorySearcher(de);
                search.Filter = "(SAMAccountName=" + NIP + ")";
                SearchResult result = search.FindOne();

                DirectoryEntry updateEntry = result.GetDirectoryEntry();

                updateEntry.Invoke("ChangePassword", new object[] { oldPass, newPass });
                updateEntry.CommitChanges();
                updateEntry.Close();

                search.Dispose();
            }
            catch (Exception ex)
            {
                CreateErrorLog("enh2 ResetPassword : " + ex.Message.ToString(), true);
                res = ex.InnerException.Message;
            }
            return res;
        }

        [WebMethod]
        public bool IS_MIGRATION_DOWNLOAD(string AO_CODE, string LOGIN_USER, string LOGIN_PASSWD)
        {
            if (IsValidUser(LOGIN_USER, LOGIN_PASSWD))
            {
                object result;
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT IS_MIGRATION_DOWNLOAD FROM TBL_MIGRATION (NOLOCK) WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                result = db.ExecuteScalar(cmd);

                if (result == null) return false;
                else if (Convert.ToString(result).Trim() == "1") return true;
            }
            return false;
        }

        [WebMethod]
        public bool IS_MIGRATION_UPLOAD(string AO_CODE, string LOGIN_USER, string LOGIN_PASSWD)
        {
            if (IsValidUser(LOGIN_USER, LOGIN_PASSWD))
            {
                object result;
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT IS_MIGRATION_UPLOAD FROM TBL_MIGRATION (NOLOCK) WHERE LTRIM(RTRIM(AO_CODE)) = @AO_CODE");

                db.AddInParameter(cmd, "@AO_CODE", DbType.String, AO_CODE);
                result = db.ExecuteScalar(cmd);

                if (result == null) return false;
                else if (Convert.ToString(result).Trim() == "1") return true;
            }
            return false;
        }

        [WebMethod]
        public int UReffealAssignedTo(DateTime AssignedDate, string REFFERAL_ID, string AO_CODE, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"UPDATE TBL_PROSPECT_REFFERAL SET IS_ASSIGNED = '1', ASSIGNED_TO = @ASSIGNED_TO, ASSIGNED_DATE = @ASSIGNED_DATE WHERE REFFERAL_ID=@REFFERAL_ID");

                db.AddInParameter(cmd, "@ASSIGNED_TO", DbType.String, AO_CODE);
                db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, REFFERAL_ID);
                db.AddInParameter(cmd, "@ASSIGNED_DATE", DbType.DateTime, AssignedDate);
                return db.ExecuteNonQuery(cmd);
            }
            return 0;
        }

        [WebMethod]
        public bool URefferalIsDownloaded(List<string> REFFERAL_ID, string LoginUser, string LoginPass)
        {
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd;
                for (int i = 0; i < REFFERAL_ID.Count; i++)
                {
                    cmd = db.GetSqlStringCommand(@"UPDATE TBL_PROSPECT_REFFERAL SET IS_DOWNLOADED = '1', DOWNLOAD_BY = @DOWNLOAD_BY, DOWNLOAD_DATE = @DOWNLOAD_DATE WHERE REFFERAL_ID = @REFFERAL_ID");

                    db.AddInParameter(cmd, "@DOWNLOAD_BY", DbType.String, LoginUser);
                    db.AddInParameter(cmd, "@DOWNLOAD_DATE", DbType.DateTime, DateTime.Now);
                    db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, REFFERAL_ID[i]);
                    db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            return false;
        }

        [WebMethod]
        public string CheckLDAPSPADAUser(string LoginUser, string LoginPass, string Unit)
        {
            string UserUnitCode = "~";

            if (IsLDAPUser(LoginUser, LoginPass))
            {
                long expired = 0;
                object result;
                expired = CheckExpired(LoginUser, LoginPass);

                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetStoredProcCommand(@"WS_CheckLDAPSPADAUser_ENH2B3");

                db.AddInParameter(cmd, "@APP_ID", DbType.String, SPADAAppCode);
                db.AddInParameter(cmd, "@NIP", DbType.String, LoginUser);
                db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, Unit);
                result = db.ExecuteScalar(cmd);

                string unit = result == null ? "na" : Convert.ToString(result);
                unit = unit == "" ? "na" : unit;
                UserUnitCode = unit + "," + SPADADBEncCode + "," + expired.ToString();

                return UserUnitCode;
            }
            else
            {
                return UserUnitCode + "," + SPADADBEncCode + ",0";
            }
        }

        [WebMethod]
        public byte[] GetProspecByDate(string table, string unit, string ao_code, DateTime start, DateTime end, string LoginUser, string LoginPass)
        {
            DataSet ds = new DataSet();
            if (IsValidUser(LoginUser, LoginPass))
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("WS_GET_PROSPECT_BY_DATE");

                cmd.CommandTimeout = 1800;
                db.AddInParameter(cmd, "@ID", DbType.String, table);
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit);
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, ao_code);
                db.AddInParameter(cmd, "@DATE_START", DbType.DateTime, start);
                db.AddInParameter(cmd, "@DATE_END", DbType.DateTime, end);

                ds = db.ExecuteDataSet(cmd);
            }
            return TransformIntoBytes(ds);
        }

        [WebMethod]
        public string UploadProspect(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_TMP
                                SET 
                                    PROSPECT_NO = @PROSPECT_NO, UNIT_CODE = @UNIT_CODE, SOURCE_CODE = @SOURCE_CODE
                                    ,AO_CODE = @AO_CODE, FULL_NAME = @FULL_NAME, KTP_NAME = @KTP_NAME
                                    ,KTP_NO = @KTP_NO, DATE_OF_BIRTH = @DATE_OF_BIRTH, PLACE_OF_BIRTH = @PLACE_OF_BIRTH
                                    ,MOTHER_NAME = @MOTHER_NAME, MALE_FEMALE = @MALE_FEMALE, MARITAL_STATUS = @MARITAL_STATUS
                                    ,SPOUSE_NAME = @SPOUSE_NAME, SPOUSE_DATE_OF_BIRTH = @SPOUSE_DATE_OF_BIRTH, NPWP_NO = @NPWP_NO
                                    ,KODYA_KAB_BUSINESS = @KODYA_KAB_BUSINESS, KECAMATAN_BUSINESS = @KECAMATAN_BUSINESS, KELURAHAN_BUSINESS = @KELURAHAN_BUSINESS
                                    ,ZIP_CODE_BUSINESS = @ZIP_CODE_BUSINESS, ADDRESS_BUSINESS = @ADDRESS_BUSINESS, RESIDENTIAL_STATUS = @RESIDENTIAL_STATUS
                                    ,RESIDENTIAL_PHONE = @RESIDENTIAL_PHONE, MOBILE_PHONE = @MOBILE_PHONE, OFFICE_STATUS = @OFFICE_STATUS
                                    ,OFFICE_PHONE = @OFFICE_PHONE, YEAR_OF_STAY = @YEAR_OF_STAY, JML_TANGGUNGAN = @JML_TANGGUNGAN
                                    ,JENIS_USAHA = @JENIS_USAHA, LAMA_USAHA = @LAMA_USAHA, OMSET_PER_HARI = @OMSET_PER_HARI
                                    ,ADA_REK_BANK = @ADA_REK_BANK, RENCANA_PLAFON = @RENCANA_PLAFON, SALES_CHANNEL = @SALES_CHANNEL
                                    ,PROSPECT_TYPE = @PROSPECT_TYPE, FUNDING_LENDING = @FUNDING_LENDING, RESTRUCT_COMPONENT = @RESTRUCT_COMPONENT
                                    ,PROGRESS_STATUS = @PROGRESS_STATUS, CYCLE_STATUS = @CYCLE_STATUS, INSTALLMENT = @INSTALLMENT
                                    ,FAP_DATE = @FAP_DATE, NO_OF_CALLS = @NO_OF_CALLS, IS_INTERESTED = @IS_INTERESTED
                                    ,NOT_INTERESTED_REASON = @NOT_INTERESTED_REASON, NOT_INTERESTED_NOTE = @NOT_INTERESTED_NOTE, KET_KENDALA = @KET_KENDALA
                                    ,SCM_NOTE = @SCM_NOTE, PRODUCT_TYPE = @PRODUCT_TYPE, CIF_NUMBER = @CIF_NUMBER
                                    ,KARAKTER = @KARAKTER, REPUTASI_DEBITUR = @REPUTASI_DEBITUR, RIWAYAT_DSP = @RIWAYAT_DSP
                                    ,TAKSIRAN_PLAFON = @TAKSIRAN_PLAFON, TAKSIRAN_USAHA = @TAKSIRAN_USAHA, IS_DOCUMENT_COMPLETED = @IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION = @UM_DECISION, UM_DECISION_NOTE = @UM_DECISION_NOTE, UM_DECISION_DATE = @UM_DECISION_DATE
                                    ,LOAN_NUMBER = @LOAN_NUMBER, PRODUCT_CODE = @PRODUCT_CODE, CURRENT_OS = @CURRENT_OS
                                    ,CURRENT_INSTALLMENT = @CURRENT_INSTALLMENT, OFFER_PLAFON = @OFFER_PLAFON, OFFER_INSTALLMENT = @OFFER_INSTALLMENT
                                    ,OFFER_INTEREST = @OFFER_INTEREST, OFFER_TENOR = @OFFER_TENOR, OFFER_FRESH_MONEY = @OFFER_FRESH_MONEY
                                    ,PRIORITY = @PRIORITY, PIPELINE_START_TIME = @PIPELINE_START_TIME, PIPELINE_FINISH_TIME = @PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME = @PROSPECT_START_TIME, PROSPECT_FINISH_TIME = @PROSPECT_FINISH_TIME, BI_GOOD_UNTIL = @BI_GOOD_UNTIL
                                    ,IS_BI_CHECKING = @IS_BI_CHECKING, IS_OFFLINE_ENTRY = @IS_OFFLINE_ENTRY, ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE, UPDATE_BY = @UPDATE_BY, UPDATE_DATE = @UPDATE_DATE
                                    ,IS_BY_UPLOAD = @IS_BY_UPLOAD, UPLOAD_BY = @UPLOAD_BY, UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_DELETED = @IS_DELETED, DEBTOR_TYPE = @DEBTOR_TYPE, GU_FUP = @GU_FUP
                                    ,GU_CLOSE_DEAL = @GU_CLOSE_DEAL, IS_DEDUP = @IS_DEDUP, CO_ASSIGNED = @CO_ASSIGNED
                                    ,CHANNELING = @CHANNELING, OMZET_1 = @OMZET_1, OMZET_2 = @OMZET_2
                                    ,OMZET_3 = @OMZET_3, OMZET_4 = @OMZET_4, OMZET_5 = @OMZET_5
                                    ,OMZET_6 = @OMZET_6, OMZET_7 = @OMZET_7, OMZET_8 = @OMZET_8
                                    ,OMZET_9 = @OMZET_9, OMZET_10 = @OMZET_10, OMZET_11 = @OMZET_11
                                    ,OMZET_12 = @OMZET_12, OMZET_AVERAGE = @OMZET_AVERAGE, IS_PIPELINE_KP = @IS_PIPELINE_KP
                                    ,[EKSTERNAL] = @EKSTERNAL, REFERENSI = @REFERENSI, RES_1 = @RES_1
                                    ,RES_2 = @RES_2, RES_3 = @RES_3, RES_4 = @RES_4
                                    ,RES_5 = @RES_5, RES_6 = @RES_6, RES_7 = @RES_7
                                    ,RES_8 = @RES_8, RES_9 = @RES_9, RES_10 = @RES_10
                                    ,RES_11 = @RES_11, RES_12 = @RES_12, RES_13 = @RES_13
                                    ,RES_14 = @RES_14, RES_15 = @RES_15, RES_16 = @RES_16
                                    ,RES_17 = @RES_17, RES_18 = @RES_18, RES_19 = @RES_19
                                    ,RES_20 = @RES_20, RES_21 = @RES_21, RES_22 = @RES_22
                                    ,RES_23 = @RES_23, RES_24 = @RES_24, RES_25 = @RES_25
                                    ,RES_26 = @RES_26, RES_27 = @RES_27, RES_28 = @RES_28
                                    ,RES_29 = @RES_29, RES_30 = @RES_30, RES_31 = @RES_31
                                    ,RES_32 = @RES_32, RES_33 = @RES_33, RES_34 = @RES_34
                                    ,RES_35 = @RES_35, RES_36 = @RES_36, RES_37 = @RES_37
                                    ,RES_38 = @RES_38, RES_39 = @RES_39, RES_40 = @RES_40
                                    ,RES_41 = @RES_41, RES_42 = @RES_42, RES_43 = @RES_43
                                    ,RES_44 = @RES_44, RES_45 = @RES_45, RES_46 = @RES_46
                                    ,RES_47 = @RES_47, RES_48 = @RES_48, RES_49 = @RES_49
                                    ,RES_50 = @RES_50, RES_51 = @RES_51, RES_52 = @RES_52
                                    ,RES_53 = @RES_53, RES_54 = @RES_54, RES_55 = @RES_55
                                    ,RES_56 = @RES_56, RES_57 = @RES_57, RES_58 = @RES_58
                                    ,RES_59 = @RES_59, RES_60 = @RES_60, RES_61 = @RES_61
                                    ,RES_62 = @RES_62, RES_63 = @RES_63, RES_64 = @RES_64
                                    ,RES_65 = @RES_65, RES_66 = @RES_66, RES_67 = @RES_67
                                    ,RES_68 = @RES_68, RES_69 = @RES_69, RES_70 = @RES_70
                                    ,RES_71 = @RES_71, RES_72 = @RES_72, RES_73 = @RES_73
                                    ,RES_74 = @RES_74, RES_75 = @RES_75, RES_76 = @RES_76
                                    ,RES_77 = @RES_77, RES_78 = @RES_78, RES_79 = @RES_79
                                    ,RES_80 = @RES_80, RES_81 = @RES_81, RES_82 = @RES_82
                                    ,RES_83 = @RES_83, RES_84 = @RES_84, RES_85 = @RES_85
                                    ,RES_86 = @RES_86, RES_87 = @RES_87, RES_88 = @RES_88
                                    ,RES_89 = @RES_89, RES_90 = @RES_90, RES_91 = @RES_91
                                    ,RES_92 = @RES_92, RES_93 = @RES_93, RES_94 = @RES_94
                                    ,RES_95 = @RES_95, RES_96 = @RES_96, RES_97 = @RES_97
                                    ,RES_98 = @RES_98, RES_99 = @RES_99, RES_100 = @RES_100
                                    ,NIP = @NIP, OLD_NIP = @OLD_NIP, OLD_AO_CODE = @OLD_AO_CODE
                                    ,ALTERNATE_FOR = @ALTERNATE_FOR, IS_UPLOAD = @IS_UPLOAD, FLAG_FREEZE = @FLAG_FREEZE
                                    ,KTP_ADDRESS = @KTP_ADDRESS, AVG_EXPENSE = @AVG_EXPENSE
                                    ,NAMA_TIDAK_SINGKAT=@NAMA_TIDAK_SINGKAT
                                    ,LAST_UPDATE_FROM = @LAST_UPDATE_FROM
                                WHERE (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_TMP
                                (
                                    PROSPECT_ID,PROSPECT_NO,UNIT_CODE,SOURCE_CODE,AO_CODE
                                    ,FULL_NAME,KTP_NAME,KTP_NO,DATE_OF_BIRTH,PLACE_OF_BIRTH
                                    ,MOTHER_NAME,MALE_FEMALE,MARITAL_STATUS,SPOUSE_NAME,SPOUSE_DATE_OF_BIRTH
                                    ,NPWP_NO,KODYA_KAB_BUSINESS,KECAMATAN_BUSINESS,KELURAHAN_BUSINESS,ZIP_CODE_BUSINESS
                                    ,ADDRESS_BUSINESS,RESIDENTIAL_STATUS,RESIDENTIAL_PHONE,MOBILE_PHONE,OFFICE_STATUS
                                    ,OFFICE_PHONE,YEAR_OF_STAY,JML_TANGGUNGAN,JENIS_USAHA,LAMA_USAHA
                                    ,OMSET_PER_HARI,ADA_REK_BANK,RENCANA_PLAFON,SALES_CHANNEL,PROSPECT_TYPE
                                    ,FUNDING_LENDING,RESTRUCT_COMPONENT,PROGRESS_STATUS,CYCLE_STATUS,INSTALLMENT
                                    ,FAP_DATE,NO_OF_CALLS,IS_INTERESTED,NOT_INTERESTED_REASON,NOT_INTERESTED_NOTE
                                    ,KET_KENDALA,SCM_NOTE,PRODUCT_TYPE,CIF_NUMBER,KARAKTER
                                    ,REPUTASI_DEBITUR,RIWAYAT_DSP,TAKSIRAN_PLAFON,TAKSIRAN_USAHA,IS_DOCUMENT_COMPLETED
                                    ,UM_DECISION,UM_DECISION_NOTE,UM_DECISION_DATE,LOAN_NUMBER,PRODUCT_CODE
                                    ,CURRENT_OS,CURRENT_INSTALLMENT,OFFER_PLAFON,OFFER_INSTALLMENT,OFFER_INTEREST
                                    ,OFFER_TENOR,OFFER_FRESH_MONEY,PRIORITY,PIPELINE_START_TIME,PIPELINE_FINISH_TIME
                                    ,PROSPECT_START_TIME,PROSPECT_FINISH_TIME,BI_GOOD_UNTIL,IS_BI_CHECKING,IS_OFFLINE_ENTRY
                                    ,ENTRY_BY,ENTRY_DATE,UPDATE_BY,UPDATE_DATE,IS_BY_UPLOAD
                                    ,UPLOAD_BY,UPLOAD_DATE,IS_DELETED,DEBTOR_TYPE,GU_FUP
                                    ,GU_CLOSE_DEAL,IS_DEDUP,CO_ASSIGNED,CHANNELING,OMZET_1
                                    ,OMZET_2,OMZET_3,OMZET_4,OMZET_5,OMZET_6
                                    ,OMZET_7,OMZET_8,OMZET_9,OMZET_10,OMZET_11
                                    ,OMZET_12,OMZET_AVERAGE,IS_PIPELINE_KP,[EKSTERNAL],REFERENSI
                                    ,RES_1,RES_2,RES_3,RES_4,RES_5
                                    ,RES_6,RES_7,RES_8,RES_9,RES_10
                                    ,RES_11,RES_12,RES_13,RES_14,RES_15
                                    ,RES_16,RES_17,RES_18,RES_19,RES_20
                                    ,RES_21,RES_22,RES_23,RES_24,RES_25
                                    ,RES_26,RES_27,RES_28,RES_29,RES_30
                                    ,RES_31,RES_32,RES_33,RES_34,RES_35
                                    ,RES_36,RES_37,RES_38,RES_39,RES_40
                                    ,RES_41,RES_42,RES_43,RES_44,RES_45
                                    ,RES_46,RES_47,RES_48,RES_49,RES_50
                                    ,RES_51,RES_52,RES_53,RES_54,RES_55
                                    ,RES_56,RES_57,RES_58,RES_59,RES_60
                                    ,RES_61,RES_62,RES_63,RES_64,RES_65
                                    ,RES_66,RES_67,RES_68,RES_69,RES_70
                                    ,RES_71,RES_72,RES_73,RES_74,RES_75
                                    ,RES_76,RES_77,RES_78,RES_79,RES_80
                                    ,RES_81,RES_82,RES_83,RES_84,RES_85
                                    ,RES_86,RES_87,RES_88,RES_89,RES_90
                                    ,RES_91,RES_92,RES_93,RES_94,RES_95
                                    ,RES_96,RES_97,RES_98,RES_99,RES_100
                                    ,NIP,OLD_NIP,OLD_AO_CODE,ALTERNATE_FOR,IS_UPLOAD
                                    ,FLAG_FREEZE
                                    ,NAMA_TIDAK_SINGKAT
                                    ,KTP_ADDRESS, AVG_EXPENSE
                                    ,LAST_UPDATE_FROM) 
                                VALUES
                                (
                                    @PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@SOURCE_CODE,@AO_CODE
                                    ,@FULL_NAME,@KTP_NAME,@KTP_NO,@DATE_OF_BIRTH,@PLACE_OF_BIRTH
                                    ,@MOTHER_NAME,@MALE_FEMALE,@MARITAL_STATUS,@SPOUSE_NAME,@SPOUSE_DATE_OF_BIRTH
                                    ,@NPWP_NO,@KODYA_KAB_BUSINESS,@KECAMATAN_BUSINESS,@KELURAHAN_BUSINESS,@ZIP_CODE_BUSINESS
                                    ,@ADDRESS_BUSINESS,@RESIDENTIAL_STATUS,@RESIDENTIAL_PHONE,@MOBILE_PHONE,@OFFICE_STATUS
                                    ,@OFFICE_PHONE,@YEAR_OF_STAY,@JML_TANGGUNGAN,@JENIS_USAHA,@LAMA_USAHA
                                    ,@OMSET_PER_HARI,@ADA_REK_BANK,@RENCANA_PLAFON,@SALES_CHANNEL,@PROSPECT_TYPE
                                    ,@FUNDING_LENDING,@RESTRUCT_COMPONENT,@PROGRESS_STATUS,@CYCLE_STATUS,@INSTALLMENT
                                    ,@FAP_DATE,@NO_OF_CALLS,@IS_INTERESTED,@NOT_INTERESTED_REASON,@NOT_INTERESTED_NOTE
                                    ,@KET_KENDALA,@SCM_NOTE,@PRODUCT_TYPE,@CIF_NUMBER,@KARAKTER
                                    ,@REPUTASI_DEBITUR,@RIWAYAT_DSP,@TAKSIRAN_PLAFON,@TAKSIRAN_USAHA,@IS_DOCUMENT_COMPLETED
                                    ,@UM_DECISION,@UM_DECISION_NOTE,@UM_DECISION_DATE,@LOAN_NUMBER,@PRODUCT_CODE
                                    ,@CURRENT_OS,@CURRENT_INSTALLMENT,@OFFER_PLAFON,@OFFER_INSTALLMENT,@OFFER_INTEREST
                                    ,@OFFER_TENOR,@OFFER_FRESH_MONEY,@PRIORITY,@PIPELINE_START_TIME,@PIPELINE_FINISH_TIME
                                    ,@PROSPECT_START_TIME,@PROSPECT_FINISH_TIME,@BI_GOOD_UNTIL,@IS_BI_CHECKING,@IS_OFFLINE_ENTRY
                                    ,@ENTRY_BY,@ENTRY_DATE,@UPDATE_BY,@UPDATE_DATE,@IS_BY_UPLOAD
                                    ,@UPLOAD_BY,@UPLOAD_DATE,@IS_DELETED,@DEBTOR_TYPE,@GU_FUP
                                    ,@GU_CLOSE_DEAL,@IS_DEDUP,@CO_ASSIGNED,@CHANNELING,@OMZET_1
                                    ,@OMZET_2,@OMZET_3,@OMZET_4,@OMZET_5,@OMZET_6
                                    ,@OMZET_7,@OMZET_8,@OMZET_9,@OMZET_10,@OMZET_11
                                    ,@OMZET_12,@OMZET_AVERAGE,@IS_PIPELINE_KP,@EKSTERNAL,@REFERENSI
                                    ,@RES_1,@RES_2,@RES_3,@RES_4,@RES_5
                                    ,@RES_6,@RES_7,@RES_8,@RES_9,@RES_10
                                    ,@RES_11,@RES_12,@RES_13,@RES_14,@RES_15
                                    ,@RES_16,@RES_17,@RES_18,@RES_19,@RES_20
                                    ,@RES_21,@RES_22,@RES_23,@RES_24,@RES_25
                                    ,@RES_26,@RES_27,@RES_28,@RES_29,@RES_30
                                    ,@RES_31,@RES_32,@RES_33,@RES_34,@RES_35
                                    ,@RES_36,@RES_37,@RES_38,@RES_39,@RES_40
                                    ,@RES_41,@RES_42,@RES_43,@RES_44,@RES_45
                                    ,@RES_46,@RES_47,@RES_48,@RES_49,@RES_50
                                    ,@RES_51,@RES_52,@RES_53,@RES_54,@RES_55
                                    ,@RES_56,@RES_57,@RES_58,@RES_59,@RES_60
                                    ,@RES_61,@RES_62,@RES_63,@RES_64,@RES_65
                                    ,@RES_66,@RES_67,@RES_68,@RES_69,@RES_70
                                    ,@RES_71,@RES_72,@RES_73,@RES_74,@RES_75
                                    ,@RES_76,@RES_77,@RES_78,@RES_79,@RES_80
                                    ,@RES_81,@RES_82,@RES_83,@RES_84,@RES_85
                                    ,@RES_86,@RES_87,@RES_88,@RES_89,@RES_90
                                    ,@RES_91,@RES_92,@RES_93,@RES_94,@RES_95
                                    ,@RES_96,@RES_97,@RES_98,@RES_99,@RES_100
                                    ,@NIP,@OLD_NIP,@OLD_AO_CODE,@ALTERNATE_FOR,@IS_UPLOAD
                                    ,@FLAG_FREEZE
                                    ,@NAMA_TIDAK_SINGKAT
                                    ,@KTP_ADDRESS, @AVG_EXPENSE
                                    ,@LAST_UPDATE_FROM
                                );
                            ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                        db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, Convert.ToString(dr["SOURCE_CODE"]));
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                        db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                        db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                        db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@MOTHER_NAME", DbType.String, Convert.ToString(dr["MOTHER_NAME"]));
                        db.AddInParameter(cmd, "@MALE_FEMALE", DbType.String, Convert.ToString(dr["MALE_FEMALE"]));
                        db.AddInParameter(cmd, "@MARITAL_STATUS", DbType.String, Convert.ToString(dr["MARITAL_STATUS"]));
                        db.AddInParameter(cmd, "@SPOUSE_NAME", DbType.String, Convert.ToString(dr["SPOUSE_NAME"]));
                        db.AddInParameter(cmd, "@SPOUSE_DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["SPOUSE_DATE_OF_BIRTH"]));
                        db.AddInParameter(cmd, "@NPWP_NO", DbType.String, Convert.ToString(dr["NPWP_NO"]));
                        db.AddInParameter(cmd, "@KODYA_KAB_BUSINESS", DbType.String, Convert.ToString(dr["KODYA_KAB_BUSINESS"]));
                        db.AddInParameter(cmd, "@KECAMATAN_BUSINESS", DbType.String, Convert.ToString(dr["KECAMATAN_BUSINESS"]));
                        db.AddInParameter(cmd, "@KELURAHAN_BUSINESS", DbType.String, Convert.ToString(dr["KELURAHAN_BUSINESS"]));
                        db.AddInParameter(cmd, "@ZIP_CODE_BUSINESS", DbType.String, Convert.ToString(dr["ZIP_CODE_BUSINESS"]));
                        db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                        db.AddInParameter(cmd, "@RESIDENTIAL_STATUS", DbType.String, Convert.ToString(dr["RESIDENTIAL_STATUS"]));
                        db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                        db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                        db.AddInParameter(cmd, "@OFFICE_STATUS", DbType.String, Convert.ToString(dr["OFFICE_STATUS"]));
                        db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                        db.AddInParameter(cmd, "@YEAR_OF_STAY", DbType.Int16, Convert.ToInt16(dr["YEAR_OF_STAY"]));
                        db.AddInParameter(cmd, "@JML_TANGGUNGAN", DbType.Int16, Convert.ToInt16(dr["JML_TANGGUNGAN"]));
                        db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, Convert.ToString(dr["JENIS_USAHA"]));
                        db.AddInParameter(cmd, "@LAMA_USAHA", DbType.String, Convert.ToString(dr["LAMA_USAHA"]));
                        db.AddInParameter(cmd, "@OMSET_PER_HARI", DbType.Int32, Convert.ToInt32(dr["OMSET_PER_HARI"]));
                        db.AddInParameter(cmd, "@ADA_REK_BANK", DbType.String, Convert.ToString(dr["ADA_REK_BANK"]));
                        db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, Convert.ToDouble(dr["RENCANA_PLAFON"]));
                        db.AddInParameter(cmd, "@SALES_CHANNEL", DbType.String, Convert.ToString(dr["SALES_CHANNEL"]));
                        db.AddInParameter(cmd, "@PROSPECT_TYPE", DbType.String, Convert.ToString(dr["PROSPECT_TYPE"]));
                        db.AddInParameter(cmd, "@FUNDING_LENDING", DbType.String, Convert.ToString(dr["FUNDING_LENDING"]));
                        db.AddInParameter(cmd, "@RESTRUCT_COMPONENT", DbType.String, Convert.ToString(dr["RESTRUCT_COMPONENT"]));
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                        db.AddInParameter(cmd, "@CYCLE_STATUS", DbType.String, Convert.ToString(dr["CYCLE_STATUS"]));
                        db.AddInParameter(cmd, "@INSTALLMENT", DbType.Double, Convert.ToDouble(dr["INSTALLMENT"]));
                        db.AddInParameter(cmd, "@FAP_DATE", DbType.DateTime, Convert.ToDateTime(dr["FAP_DATE"]));
                        db.AddInParameter(cmd, "@NO_OF_CALLS", DbType.Int16, Convert.ToString(dr["RESTRUCT_COMPONENT"]).Trim() == "1" ? Convert.ToInt16(dr["NO_OF_CALLS"]) + 1 : Convert.ToInt16(dr["NO_OF_CALLS"]));
                        db.AddInParameter(cmd, "@IS_INTERESTED", DbType.String, Convert.ToString(dr["IS_INTERESTED"]));
                        db.AddInParameter(cmd, "@NOT_INTERESTED_REASON", DbType.String, Convert.ToString(dr["NOT_INTERESTED_REASON"]));
                        db.AddInParameter(cmd, "@NOT_INTERESTED_NOTE", DbType.String, Convert.ToString(dr["NOT_INTERESTED_NOTE"]));
                        db.AddInParameter(cmd, "@KET_KENDALA", DbType.String, Convert.ToString(dr["KET_KENDALA"]));
                        db.AddInParameter(cmd, "@SCM_NOTE", DbType.String, Convert.ToString(dr["SCM_NOTE"]));
                        db.AddInParameter(cmd, "@PRODUCT_TYPE", DbType.Int16, Convert.ToInt16(dr["PRODUCT_TYPE"]));
                        db.AddInParameter(cmd, "@CIF_NUMBER", DbType.Int32, Convert.ToInt32(dr["CIF_NUMBER"]));
                        db.AddInParameter(cmd, "@KARAKTER", DbType.String, Convert.ToString(dr["KARAKTER"]));
                        db.AddInParameter(cmd, "@REPUTASI_DEBITUR", DbType.String, Convert.ToString(dr["REPUTASI_DEBITUR"]));
                        db.AddInParameter(cmd, "@RIWAYAT_DSP", DbType.String, Convert.ToString(dr["RIWAYAT_DSP"]));
                        db.AddInParameter(cmd, "@TAKSIRAN_PLAFON", DbType.Double, Convert.ToDouble(dr["TAKSIRAN_PLAFON"]));
                        db.AddInParameter(cmd, "@TAKSIRAN_USAHA", DbType.String, Convert.ToString(dr["TAKSIRAN_USAHA"]));
                        db.AddInParameter(cmd, "@IS_DOCUMENT_COMPLETED", DbType.String, Convert.ToString(dr["IS_DOCUMENT_COMPLETED"]));
                        db.AddInParameter(cmd, "@UM_DECISION", DbType.String, Convert.ToString(dr["UM_DECISION"]));
                        db.AddInParameter(cmd, "@UM_DECISION_NOTE", DbType.String, Convert.ToString(dr["UM_DECISION_NOTE"]));
                        db.AddInParameter(cmd, "@UM_DECISION_DATE", DbType.DateTime, Convert.ToDateTime(dr["UM_DECISION_DATE"]));
                        db.AddInParameter(cmd, "@LOAN_NUMBER", DbType.String, Convert.ToString(dr["LOAN_NUMBER"]));
                        db.AddInParameter(cmd, "@PRODUCT_CODE", DbType.String, Convert.ToString(dr["PRODUCT_CODE"]));
                        db.AddInParameter(cmd, "@CURRENT_OS", DbType.Double, Convert.ToDouble(dr["CURRENT_OS"]));
                        db.AddInParameter(cmd, "@CURRENT_INSTALLMENT", DbType.Double, Convert.ToDouble(dr["CURRENT_INSTALLMENT"]));
                        db.AddInParameter(cmd, "@OFFER_PLAFON", DbType.Double, Convert.ToDouble(dr["OFFER_PLAFON"]));
                        db.AddInParameter(cmd, "@OFFER_INSTALLMENT", DbType.Double, Convert.ToDouble(dr["OFFER_INSTALLMENT"]));
                        db.AddInParameter(cmd, "@OFFER_INTEREST", DbType.String, Convert.ToString(dr["OFFER_INTEREST"]));
                        db.AddInParameter(cmd, "@OFFER_TENOR", DbType.Int16, Convert.ToInt16(dr["OFFER_TENOR"]));
                        db.AddInParameter(cmd, "@OFFER_FRESH_MONEY", DbType.Double, Convert.ToDouble(dr["OFFER_FRESH_MONEY"]));
                        db.AddInParameter(cmd, "@PRIORITY", DbType.String, Convert.ToString(dr["PRIORITY"]));
                        db.AddInParameter(cmd, "@PIPELINE_START_TIME", DbType.DateTime, Convert.ToDateTime(dr["PIPELINE_START_TIME"]));
                        db.AddInParameter(cmd, "@PIPELINE_FINISH_TIME", DbType.DateTime, Convert.ToDateTime(dr["PIPELINE_FINISH_TIME"]));
                        db.AddInParameter(cmd, "@PROSPECT_START_TIME", DbType.DateTime, Convert.ToDateTime(dr["PROSPECT_START_TIME"]));
                        db.AddInParameter(cmd, "@PROSPECT_FINISH_TIME", DbType.DateTime, Convert.ToDateTime(dr["PROSPECT_FINISH_TIME"]));
                        db.AddInParameter(cmd, "@BI_GOOD_UNTIL", DbType.DateTime, Convert.ToDateTime(dr["BI_GOOD_UNTIL"]));
                        db.AddInParameter(cmd, "@IS_BI_CHECKING", DbType.String, Convert.ToString(dr["IS_BI_CHECKING"]));
                        db.AddInParameter(cmd, "@IS_OFFLINE_ENTRY", DbType.String, Convert.ToString(dr["IS_OFFLINE_ENTRY"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@IS_BY_UPLOAD", DbType.String, Convert.ToString(dr["IS_BY_UPLOAD"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));
                        db.AddInParameter(cmd, "@DEBTOR_TYPE", DbType.String, Convert.ToString(dr["DEBTOR_TYPE"]));
                        db.AddInParameter(cmd, "@GU_FUP", DbType.DateTime, Convert.ToDateTime(dr["GU_FUP"]));
                        db.AddInParameter(cmd, "@GU_CLOSE_DEAL", DbType.DateTime, Convert.ToDateTime(dr["GU_CLOSE_DEAL"]));
                        db.AddInParameter(cmd, "@IS_DEDUP", DbType.String, Convert.ToString(dr["IS_DEDUP"]));
                        db.AddInParameter(cmd, "@CO_ASSIGNED", DbType.String, Convert.ToString(dr["CO_ASSIGNED"]));
                        db.AddInParameter(cmd, "@CHANNELING", DbType.String, Convert.ToString(dr["CHANNELING"]));
                        db.AddInParameter(cmd, "@OMZET_1", DbType.Double, Convert.ToDouble(dr["OMZET_1"]));
                        db.AddInParameter(cmd, "@OMZET_2", DbType.Double, Convert.ToDouble(dr["OMZET_2"]));
                        db.AddInParameter(cmd, "@OMZET_3", DbType.Double, Convert.ToDouble(dr["OMZET_3"]));
                        db.AddInParameter(cmd, "@OMZET_4", DbType.Double, Convert.ToDouble(dr["OMZET_4"]));
                        db.AddInParameter(cmd, "@OMZET_5", DbType.Double, Convert.ToDouble(dr["OMZET_5"]));
                        db.AddInParameter(cmd, "@OMZET_6", DbType.Double, Convert.ToDouble(dr["OMZET_6"]));
                        db.AddInParameter(cmd, "@OMZET_7", DbType.Double, Convert.ToDouble(dr["OMZET_7"]));
                        db.AddInParameter(cmd, "@OMZET_8", DbType.Double, Convert.ToDouble(dr["OMZET_8"]));
                        db.AddInParameter(cmd, "@OMZET_9", DbType.Double, Convert.ToDouble(dr["OMZET_9"]));
                        db.AddInParameter(cmd, "@OMZET_10", DbType.Double, Convert.ToDouble(dr["OMZET_10"]));
                        db.AddInParameter(cmd, "@OMZET_11", DbType.Double, Convert.ToDouble(dr["OMZET_11"]));
                        db.AddInParameter(cmd, "@OMZET_12", DbType.Double, Convert.ToDouble(dr["OMZET_12"]));
                        db.AddInParameter(cmd, "@OMZET_AVERAGE", DbType.Double, Convert.ToDouble(dr["OMZET_AVERAGE"]));
                        db.AddInParameter(cmd, "@IS_PIPELINE_KP", DbType.String, Convert.ToString(dr["IS_PIPELINE_KP"]));
                        db.AddInParameter(cmd, "@EKSTERNAL", DbType.String, Convert.ToString(dr["EKSTERNAL"]));
                        db.AddInParameter(cmd, "@REFERENSI", DbType.String, Convert.ToString(dr["REFERENSI"]));
                        db.AddInParameter(cmd, "@RES_1", DbType.String, Convert.ToString(dr["RES_1"]));
                        db.AddInParameter(cmd, "@RES_2", DbType.String, Convert.ToString(dr["RES_2"]));
                        db.AddInParameter(cmd, "@RES_3", DbType.String, Convert.ToString(dr["RES_3"]));
                        db.AddInParameter(cmd, "@RES_4", DbType.String, Convert.ToString(dr["RES_4"]));
                        db.AddInParameter(cmd, "@RES_5", DbType.String, Convert.ToString(dr["RES_5"]));
                        db.AddInParameter(cmd, "@RES_6", DbType.String, Convert.ToString(dr["RES_6"]));
                        db.AddInParameter(cmd, "@RES_7", DbType.String, Convert.ToString(dr["RES_7"]));
                        db.AddInParameter(cmd, "@RES_8", DbType.String, Convert.ToString(dr["RES_8"]));
                        db.AddInParameter(cmd, "@RES_9", DbType.String, Convert.ToString(dr["RES_9"]));
                        db.AddInParameter(cmd, "@RES_10", DbType.String, Convert.ToString(dr["RES_10"]));
                        db.AddInParameter(cmd, "@RES_11", DbType.String, Convert.ToString(dr["RES_11"]));
                        db.AddInParameter(cmd, "@RES_12", DbType.String, Convert.ToString(dr["RES_12"]));
                        db.AddInParameter(cmd, "@RES_13", DbType.String, Convert.ToString(dr["RES_13"]));
                        db.AddInParameter(cmd, "@RES_14", DbType.String, Convert.ToString(dr["RES_14"]));
                        db.AddInParameter(cmd, "@RES_15", DbType.String, Convert.ToString(dr["RES_15"]));
                        db.AddInParameter(cmd, "@RES_16", DbType.String, Convert.ToString(dr["RES_16"]));
                        db.AddInParameter(cmd, "@RES_17", DbType.String, Convert.ToString(dr["RES_17"]));
                        db.AddInParameter(cmd, "@RES_18", DbType.String, Convert.ToString(dr["RES_18"]));
                        db.AddInParameter(cmd, "@RES_19", DbType.String, Convert.ToString(dr["RES_19"]));
                        db.AddInParameter(cmd, "@RES_20", DbType.String, Convert.ToString(dr["RES_20"]));
                        db.AddInParameter(cmd, "@RES_21", DbType.String, Convert.ToString(dr["RES_21"]));
                        db.AddInParameter(cmd, "@RES_22", DbType.String, Convert.ToString(dr["RES_22"]));
                        db.AddInParameter(cmd, "@RES_23", DbType.String, Convert.ToString(dr["RES_23"]));
                        db.AddInParameter(cmd, "@RES_24", DbType.String, Convert.ToString(dr["RES_24"]));
                        db.AddInParameter(cmd, "@RES_25", DbType.String, Convert.ToString(dr["RES_25"]));
                        db.AddInParameter(cmd, "@RES_26", DbType.DateTime, Convert.ToDateTime(dr["RES_26"]));
                        db.AddInParameter(cmd, "@RES_27", DbType.DateTime, Convert.ToDateTime(dr["RES_27"]));
                        db.AddInParameter(cmd, "@RES_28", DbType.DateTime, Convert.ToDateTime(dr["RES_28"]));
                        db.AddInParameter(cmd, "@RES_29", DbType.DateTime, Convert.ToDateTime(dr["RES_29"]));
                        db.AddInParameter(cmd, "@RES_30", DbType.DateTime, Convert.ToDateTime(dr["RES_30"]));
                        db.AddInParameter(cmd, "@RES_31", DbType.DateTime, Convert.ToDateTime(dr["RES_31"]));
                        db.AddInParameter(cmd, "@RES_32", DbType.DateTime, Convert.ToDateTime(dr["RES_32"]));
                        db.AddInParameter(cmd, "@RES_33", DbType.DateTime, Convert.ToDateTime(dr["RES_33"]));
                        db.AddInParameter(cmd, "@RES_34", DbType.DateTime, Convert.ToDateTime(dr["RES_34"]));
                        db.AddInParameter(cmd, "@RES_35", DbType.DateTime, Convert.ToDateTime(dr["RES_35"]));
                        db.AddInParameter(cmd, "@RES_36", DbType.DateTime, Convert.ToDateTime(dr["RES_36"]));
                        db.AddInParameter(cmd, "@RES_37", DbType.DateTime, Convert.ToDateTime(dr["RES_37"]));
                        db.AddInParameter(cmd, "@RES_38", DbType.DateTime, Convert.ToDateTime(dr["RES_38"]));
                        db.AddInParameter(cmd, "@RES_39", DbType.DateTime, Convert.ToDateTime(dr["RES_39"]));
                        db.AddInParameter(cmd, "@RES_40", DbType.DateTime, Convert.ToDateTime(dr["RES_40"]));
                        db.AddInParameter(cmd, "@RES_41", DbType.DateTime, Convert.ToDateTime(dr["RES_41"]));
                        db.AddInParameter(cmd, "@RES_42", DbType.DateTime, Convert.ToDateTime(dr["RES_42"]));
                        db.AddInParameter(cmd, "@RES_43", DbType.DateTime, Convert.ToDateTime(dr["RES_43"]));
                        db.AddInParameter(cmd, "@RES_44", DbType.DateTime, Convert.ToDateTime(dr["RES_44"]));
                        db.AddInParameter(cmd, "@RES_45", DbType.DateTime, Convert.ToDateTime(dr["RES_45"]));
                        db.AddInParameter(cmd, "@RES_46", DbType.DateTime, Convert.ToDateTime(dr["RES_46"]));
                        db.AddInParameter(cmd, "@RES_47", DbType.DateTime, Convert.ToDateTime(dr["RES_47"]));
                        db.AddInParameter(cmd, "@RES_48", DbType.DateTime, Convert.ToDateTime(dr["RES_48"]));
                        db.AddInParameter(cmd, "@RES_49", DbType.DateTime, Convert.ToDateTime(dr["RES_49"]));
                        db.AddInParameter(cmd, "@RES_50", DbType.DateTime, Convert.ToDateTime(dr["RES_50"]));
                        db.AddInParameter(cmd, "@RES_51", DbType.Double, Convert.ToDouble(dr["RES_51"]));
                        db.AddInParameter(cmd, "@RES_52", DbType.Double, Convert.ToDouble(dr["RES_52"]));
                        db.AddInParameter(cmd, "@RES_53", DbType.Double, Convert.ToDouble(dr["RES_53"]));
                        db.AddInParameter(cmd, "@RES_54", DbType.Double, Convert.ToDouble(dr["RES_54"]));
                        db.AddInParameter(cmd, "@RES_55", DbType.Double, Convert.ToDouble(dr["RES_55"]));
                        db.AddInParameter(cmd, "@RES_56", DbType.Double, Convert.ToDouble(dr["RES_56"]));
                        db.AddInParameter(cmd, "@RES_57", DbType.Double, Convert.ToDouble(dr["RES_57"]));
                        db.AddInParameter(cmd, "@RES_58", DbType.Double, Convert.ToDouble(dr["RES_58"]));
                        db.AddInParameter(cmd, "@RES_59", DbType.Double, Convert.ToDouble(dr["RES_59"]));
                        db.AddInParameter(cmd, "@RES_60", DbType.Double, Convert.ToDouble(dr["RES_60"]));
                        db.AddInParameter(cmd, "@RES_61", DbType.Double, Convert.ToDouble(dr["RES_61"]));
                        db.AddInParameter(cmd, "@RES_62", DbType.Double, Convert.ToDouble(dr["RES_62"]));
                        db.AddInParameter(cmd, "@RES_63", DbType.Double, Convert.ToDouble(dr["RES_63"]));
                        db.AddInParameter(cmd, "@RES_64", DbType.Double, Convert.ToDouble(dr["RES_64"]));
                        db.AddInParameter(cmd, "@RES_65", DbType.Double, Convert.ToDouble(dr["RES_65"]));
                        db.AddInParameter(cmd, "@RES_66", DbType.Double, Convert.ToDouble(dr["RES_66"]));
                        db.AddInParameter(cmd, "@RES_67", DbType.Double, Convert.ToDouble(dr["RES_67"]));
                        db.AddInParameter(cmd, "@RES_68", DbType.Double, Convert.ToDouble(dr["RES_68"]));
                        db.AddInParameter(cmd, "@RES_69", DbType.Double, Convert.ToDouble(dr["RES_69"]));
                        db.AddInParameter(cmd, "@RES_70", DbType.Double, Convert.ToDouble(dr["RES_70"]));
                        db.AddInParameter(cmd, "@RES_71", DbType.Double, Convert.ToDouble(dr["RES_71"]));
                        db.AddInParameter(cmd, "@RES_72", DbType.Double, Convert.ToDouble(dr["RES_72"]));
                        db.AddInParameter(cmd, "@RES_73", DbType.Double, Convert.ToDouble(dr["RES_73"]));
                        db.AddInParameter(cmd, "@RES_74", DbType.Double, Convert.ToDouble(dr["RES_74"]));
                        db.AddInParameter(cmd, "@RES_75", DbType.Double, Convert.ToDouble(dr["RES_75"]));
                        db.AddInParameter(cmd, "@RES_76", DbType.String, Convert.ToString(dr["RES_76"]));
                        db.AddInParameter(cmd, "@RES_77", DbType.String, Convert.ToString(dr["RES_77"]));
                        db.AddInParameter(cmd, "@RES_78", DbType.String, Convert.ToString(dr["RES_78"]));
                        db.AddInParameter(cmd, "@RES_79", DbType.String, Convert.ToString(dr["RES_79"]));
                        db.AddInParameter(cmd, "@RES_80", DbType.String, Convert.ToString(dr["RES_80"]));
                        db.AddInParameter(cmd, "@RES_81", DbType.String, Convert.ToString(dr["RES_81"]));
                        db.AddInParameter(cmd, "@RES_82", DbType.String, Convert.ToString(dr["RES_82"]));
                        db.AddInParameter(cmd, "@RES_83", DbType.String, Convert.ToString(dr["RES_83"]));
                        db.AddInParameter(cmd, "@RES_84", DbType.String, Convert.ToString(dr["RES_84"]));
                        db.AddInParameter(cmd, "@RES_85", DbType.String, Convert.ToString(dr["RES_85"]));
                        db.AddInParameter(cmd, "@RES_86", DbType.String, Convert.ToString(dr["RES_86"]));
                        db.AddInParameter(cmd, "@RES_87", DbType.String, Convert.ToString(dr["RES_87"]));
                        db.AddInParameter(cmd, "@RES_88", DbType.String, Convert.ToString(dr["RES_88"]));
                        db.AddInParameter(cmd, "@RES_89", DbType.String, Convert.ToString(dr["RES_89"]));
                        db.AddInParameter(cmd, "@RES_90", DbType.String, Convert.ToString(dr["RES_90"]));
                        db.AddInParameter(cmd, "@RES_91", DbType.String, Convert.ToString(dr["RES_91"]));
                        db.AddInParameter(cmd, "@RES_92", DbType.String, Convert.ToString(dr["RES_92"]));
                        db.AddInParameter(cmd, "@RES_93", DbType.String, Convert.ToString(dr["RES_93"]));
                        db.AddInParameter(cmd, "@RES_94", DbType.String, Convert.ToString(dr["RES_94"]));
                        db.AddInParameter(cmd, "@RES_95", DbType.String, Convert.ToString(dr["RES_95"]));
                        db.AddInParameter(cmd, "@RES_96", DbType.String, Convert.ToString(dr["RES_96"]));
                        db.AddInParameter(cmd, "@RES_97", DbType.String, Convert.ToString(dr["RES_97"]));
                        db.AddInParameter(cmd, "@RES_98", DbType.String, Convert.ToString(dr["RES_98"]));
                        db.AddInParameter(cmd, "@RES_99", DbType.String, Convert.ToString(dr["RES_99"]));
                        db.AddInParameter(cmd, "@RES_100", DbType.String, Convert.ToString(dr["RES_100"]));
                        db.AddInParameter(cmd, "@NIP", DbType.String, Convert.ToString(dr["NIP"]));
                        db.AddInParameter(cmd, "@OLD_NIP", DbType.String, Convert.ToString(dr["OLD_NIP"]));
                        db.AddInParameter(cmd, "@OLD_AO_CODE", DbType.String, Convert.ToString(dr["OLD_AO_CODE"]));
                        db.AddInParameter(cmd, "@ALTERNATE_FOR", DbType.String, Convert.ToString(dr["ALTERNATE_FOR"]));
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@FLAG_FREEZE", DbType.Int16, Convert.ToInt16(dr["FLAG_FREEZE"]));

                        db.AddInParameter(cmd, "@NAMA_TIDAK_SINGKAT", DbType.String, Convert.ToString(dr["NAMA_TIDAK_SINGKAT"]));
                        db.AddInParameter(cmd, "@KTP_ADDRESS", DbType.String, Convert.ToString(dr["KTP_ADDRESS"]));
                        db.AddInParameter(cmd, "@AVG_EXPENSE", DbType.Double, Convert.ToDouble(dr["AVG_EXPENSE"]));
                        db.AddInParameter(cmd, "@LAST_UPDATE_FROM", DbType.String, "0");

                        if (db.ExecuteNonQuery(cmd) > 0)
                        {
                            result++;

                            cmd = db.GetSqlStringCommand(@"
                               IF (@PROGRESS_STATUS = 'RJS' OR @PROGRESS_STATUS = 'RJD')
                               BEGIN
                                   DELETE FROM TBL_PROSPECT_DEDUP 
                                   WHERE [PROSPECT_ID] = @PROSPECT_ID
                               END

                               ELSE

                               BEGIN
                                UPDATE [TBL_PROSPECT_DEDUP]
                                   SET [PROSPECT_NO] = @PROSPECT_NO
                                      ,[UNIT_CODE] = @UNIT_CODE
                                      ,[SOURCE_CODE] = @SOURCE_CODE
                                      ,[AO_CODE] = @AO_CODE
                                      ,[KTP_NAME] = @KTP_NAME
                                      ,[KTP_NO] = @KTP_NO
                                      ,[DATE_OF_BIRTH] = @DATE_OF_BIRTH
                                      ,[PLACE_OF_BIRTH] = @PLACE_OF_BIRTH
                                      ,[ADDRESS_BUSINESS] = @ADDRESS_BUSINESS
                                      ,[RESIDENTIAL_PHONE] = @RESIDENTIAL_PHONE
                                      ,[MOBILE_PHONE] = @MOBILE_PHONE
                                      ,[OFFICE_PHONE] = @OFFICE_PHONE
                                      ,[PRODUCT_TYPE] = @PRODUCT_TYPE
                                      ,[PROGRESS_STATUS] = @PROGRESS_STATUS
                                      ,[UPDATE_DATE] = @UPDATE_DATE
                                 WHERE [PROSPECT_ID] = @PROSPECT_ID

                                 IF @@ROWCOUNT = 0
                                 INSERT INTO [TBL_PROSPECT_DEDUP]
                                    ([PROSPECT_ID],[PROSPECT_NO],[UNIT_CODE],[SOURCE_CODE],[AO_CODE]
                                    ,[KTP_NAME],[KTP_NO],[DATE_OF_BIRTH],[PLACE_OF_BIRTH],[ADDRESS_BUSINESS]
                                    ,[RESIDENTIAL_PHONE],[MOBILE_PHONE],[OFFICE_PHONE],[PRODUCT_TYPE],[PROGRESS_STATUS]
                                    ,[UPDATE_DATE])
                                 VALUES
                                   (@PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@SOURCE_CODE,@AO_CODE
                                   ,@KTP_NAME,@KTP_NO,@DATE_OF_BIRTH,@PLACE_OF_BIRTH,@ADDRESS_BUSINESS
                                   ,@RESIDENTIAL_PHONE,@MOBILE_PHONE,@OFFICE_PHONE,@PRODUCT_TYPE,@PROGRESS_STATUS
                                   ,@UPDATE_DATE)
                               END ");

                            db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                            db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                            db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                            db.AddInParameter(cmd, "@SOURCE_CODE", DbType.String, Convert.ToString(dr["SOURCE_CODE"]));
                            db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                            db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                            db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                            db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                            db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                            db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                            db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                            db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                            db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                            db.AddInParameter(cmd, "@PRODUCT_TYPE", DbType.Int16, Convert.ToInt16(dr["PRODUCT_TYPE"]));
                            db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                            db.ExecuteNonQuery(cmd);

                            if ((Convert.ToString(dr["PROGRESS_STATUS"]).Trim() == "PCD" || Convert.ToString(dr["PROGRESS_STATUS"]).Trim() == "CD") && Convert.ToString(dr["RESTRUCT_COMPONENT"]).Trim() == "1")
                            {
                                cmd = db.GetSqlStringCommand(@"UPDATE [TBL_DEDUP_CHECKING_REQ_TMP]
                                     SET  [PROSPECT_NO] = @PROSPECT_NO, [UNIT_CODE] = @UNIT_CODE, [KTP_NAME] = @KTP_NAME,[PLACE_OF_BIRTH] = @PLACE_OF_BIRTH
                                          ,[DATE_OF_BIRTH] = @DATE_OF_BIRTH,[KTP_NO] = @KTP_NO
                                          ,[ADDRESS_BUSINESS] = @ADDRESS_BUSINESS,[MOBILE_PHONE] = @MOBILE_PHONE
                                          ,[RESIDENTIAL_PHONE] = @RESIDENTIAL_PHONE,[OFFICE_PHONE] = @OFFICE_PHONE
                                          ,[PCD_DATE] = @PCD_DATE, [NO_OF_REQUEST] = @NO_OF_REQUEST + 1
                                     WHERE [PROSPECT_ID] = @PROSPECT_ID 

                                     IF @@ROWCOUNT = 0

                                     INSERT INTO [TBL_DEDUP_CHECKING_REQ_TMP]
                                       ([PROSPECT_ID],[PROSPECT_NO],[UNIT_CODE],[KTP_NAME],[PLACE_OF_BIRTH]
                                       ,[DATE_OF_BIRTH],[KTP_NO],[ADDRESS_BUSINESS]
                                       ,[MOBILE_PHONE],[RESIDENTIAL_PHONE],[OFFICE_PHONE]
                                       ,[PCD_DATE],[NO_OF_REQUEST])
                                     VALUES
                                       (@PROSPECT_ID,@PROSPECT_NO,@UNIT_CODE,@KTP_NAME,@PLACE_OF_BIRTH
                                       ,@DATE_OF_BIRTH,@KTP_NO,@ADDRESS_BUSINESS
                                       ,@MOBILE_PHONE,@RESIDENTIAL_PHONE,@OFFICE_PHONE
                                       ,@PCD_DATE,@NO_OF_REQUEST + 1)");
                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                                db.AddInParameter(cmd, "@PROSPECT_NO", DbType.String, Convert.ToString(dr["PROSPECT_NO"]));
                                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                                db.AddInParameter(cmd, "@KTP_NAME", DbType.String, Convert.ToString(dr["KTP_NAME"]));
                                db.AddInParameter(cmd, "@PLACE_OF_BIRTH", DbType.String, Convert.ToString(dr["PLACE_OF_BIRTH"]));
                                db.AddInParameter(cmd, "@DATE_OF_BIRTH", DbType.DateTime, Convert.ToDateTime(dr["DATE_OF_BIRTH"]));
                                db.AddInParameter(cmd, "@KTP_NO", DbType.String, Convert.ToString(dr["KTP_NO"]));
                                db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                                db.AddInParameter(cmd, "@MOBILE_PHONE", DbType.String, Convert.ToString(dr["MOBILE_PHONE"]));
                                db.AddInParameter(cmd, "@RESIDENTIAL_PHONE", DbType.String, Convert.ToString(dr["RESIDENTIAL_PHONE"]));
                                db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                                db.AddInParameter(cmd, "@PCD_DATE", DbType.DateTime, Convert.ToDateTime(dr["PCD_DATE"]));
                                db.AddInParameter(cmd, "@NO_OF_REQUEST", DbType.Byte, Convert.ToByte(dr["NO_OF_CALLS"]));
                                db.ExecuteNonQuery(cmd);
                            }

                            if (Convert.ToString(dr["OLD_AO_CODE"]).Trim() != "")
                            {
                                cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_PROSPECT_UPDATE WHERE PROSPECT_ID = @PROSPECT_ID; INSERT INTO TBL_PROSPECT_UPDATE (PROSPECT_ID, UPDATE_DATE) VALUES (@PROSPECT_ID, @UPDATE_DATE)");

                                db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                                db.ExecuteNonQuery(cmd);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspect : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectAddress(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_ADDRESS_TMP 
                                SET 
                                    KODYA_KAB = @KODYA_KAB
                                    ,KECAMATAN = @KECAMATAN
                                    ,KELURAHAN = @KELURAHAN
                                    ,ZIP_CODE = @ZIP_CODE
                                    ,ADDRESS = @ADDRESS
                                    ,ADDRESS_RT = @ADDRESS_RT
                                    ,ADDRESS_RW = @ADDRESS_RW
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_UPLOAD = @IS_UPLOAD
                                    ,IS_DELETED = @IS_DELETED
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (ADDRESS_TYPE = @ADDRESS_TYPE);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_ADDRESS_TMP
                                (
                                    PROSPECT_ID
                                    ,ADDRESS_TYPE
                                    ,KODYA_KAB
                                    ,KECAMATAN
                                    ,KELURAHAN
                                    ,ZIP_CODE
                                    ,ADDRESS
                                    ,ADDRESS_RT
                                    ,ADDRESS_RW
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                    ,IS_DELETED
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@ADDRESS_TYPE
                                    ,@KODYA_KAB
                                    ,@KECAMATAN
                                    ,@KELURAHAN
                                    ,@ZIP_CODE
                                    ,@ADDRESS
                                    ,@ADDRESS_RT
                                    ,@ADDRESS_RW
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_UPLOAD
                                    ,@IS_DELETED
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@ADDRESS_TYPE", DbType.String, Convert.ToString(dr["ADDRESS_TYPE"]));
                        db.AddInParameter(cmd, "@KODYA_KAB", DbType.String, Convert.ToString(dr["KODYA_KAB"]));
                        db.AddInParameter(cmd, "@KECAMATAN", DbType.String, Convert.ToString(dr["KECAMATAN"]));
                        db.AddInParameter(cmd, "@KELURAHAN", DbType.String, Convert.ToString(dr["KELURAHAN"]));
                        db.AddInParameter(cmd, "@ZIP_CODE", DbType.String, Convert.ToString(dr["ZIP_CODE"]));
                        db.AddInParameter(cmd, "@ADDRESS", DbType.String, Convert.ToString(dr["ADDRESS"]));
                        db.AddInParameter(cmd, "@ADDRESS_RT", DbType.String, Convert.ToString(dr["ADDRESS_RT"]));
                        db.AddInParameter(cmd, "@ADDRESS_RW", DbType.String, Convert.ToString(dr["ADDRESS_RW"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));
                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectAddress : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectBank(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_BANK_HISTORY_TMP 
                                SET 
                                SIMPAN_PINJAM = @SIMPAN_PINJAM
                                ,PLAFON = @PLAFON
                                ,JANGKA_BULAN = @JANGKA_BULAN
                                ,MATURITY_DATE = @MATURITY_DATE
                                ,ENTRY_BY = @ENTRY_BY
                                ,ENTRY_DATE = @ENTRY_DATE
                                ,UPDATE_BY = @UPDATE_BY
                                ,UPDATE_DATE = @UPDATE_DATE
                                ,UPLOAD_BY = @UPLOAD_BY
                                ,UPLOAD_DATE = @UPLOAD_DATE
                                ,IS_UPLOAD = @IS_UPLOAD
                                ,IS_DELETED = @IS_DELETED
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (BANK_NAME = @BANK_NAME);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_BANK_HISTORY_TMP
                                (
                                    PROSPECT_ID
                                    ,BANK_NAME
                                    ,SIMPAN_PINJAM
                                    ,PLAFON
                                    ,JANGKA_BULAN
                                    ,MATURITY_DATE
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                    ,IS_DELETED
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@BANK_NAME
                                    ,@SIMPAN_PINJAM
                                    ,@PLAFON
                                    ,@JANGKA_BULAN
                                    ,@MATURITY_DATE
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_UPLOAD
                                    ,@IS_DELETED
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@BANK_NAME", DbType.String, Convert.ToString(dr["BANK_NAME"]));
                        db.AddInParameter(cmd, "@SIMPAN_PINJAM", DbType.String, Convert.ToString(dr["SIMPAN_PINJAM"]));
                        db.AddInParameter(cmd, "@PLAFON", DbType.Double, Convert.ToDouble(dr["PLAFON"]));
                        db.AddInParameter(cmd, "@JANGKA_BULAN", DbType.Int16, Convert.ToInt16(dr["JANGKA_BULAN"]));
                        db.AddInParameter(cmd, "@MATURITY_DATE", DbType.DateTime, Convert.ToDateTime(dr["MATURITY_DATE"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectBank : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectDoc(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_DOCUMENTS_TMP 
                                SET 
                                    ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_UPLOAD = @IS_UPLOAD
                                    ,IS_DELETED = @IS_DELETED
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (DOCUMENT_ID = @DOCUMENT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_DOCUMENTS_TMP
                                (
                                    PROSPECT_ID
                                    ,DOCUMENT_ID
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                    ,IS_DELETED
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@DOCUMENT_ID
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_UPLOAD
                                    ,@IS_DELETED
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@DOCUMENT_ID", DbType.Int16, Convert.ToInt16(dr["DOCUMENT_ID"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectDoc : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectEmgContact(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_EMG_CONTACT_TMP 
                                SET 
                                    EMG_CONTACT_NAME = @EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP = @EMG_RELATIONSHIP
                                    ,EMG_PHONE_1 = @EMG_PHONE_1
                                    ,EMG_PHONE_2 = @EMG_PHONE_2
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_UPLOAD = @IS_UPLOAD
                                    ,IS_DELETED = @IS_DELETED
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (EMG_CONTACT_ID = @EMG_CONTACT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_EMG_CONTACT_TMP
                                (
                                    PROSPECT_ID
                                    ,EMG_CONTACT_ID
                                    ,EMG_CONTACT_NAME
                                    ,EMG_RELATIONSHIP
                                    ,EMG_PHONE_1
                                    ,EMG_PHONE_2
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                    ,IS_DELETED
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@EMG_CONTACT_ID
                                    ,@EMG_CONTACT_NAME
                                    ,@EMG_RELATIONSHIP
                                    ,@EMG_PHONE_1
                                    ,@EMG_PHONE_2
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_UPLOAD
                                    ,@IS_DELETED
                                );
                              ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@EMG_CONTACT_ID", DbType.Byte, Convert.ToByte(dr["EMG_CONTACT_ID"]));
                        db.AddInParameter(cmd, "@EMG_CONTACT_NAME", DbType.String, Convert.ToString(dr["EMG_CONTACT_NAME"]));
                        db.AddInParameter(cmd, "@EMG_RELATIONSHIP", DbType.String, Convert.ToString(dr["EMG_RELATIONSHIP"]));
                        db.AddInParameter(cmd, "@EMG_PHONE_1", DbType.String, Convert.ToString(dr["EMG_PHONE_1"]));
                        db.AddInParameter(cmd, "@EMG_PHONE_2", DbType.String, Convert.ToString(dr["EMG_PHONE_2"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectEmgContact : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectVisit(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_VISIT_TMP 
                                SET 
                                    OBJECTIVE_OF_VISIT = @OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN = @DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL = @DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK = @JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT = @RESULT_OF_VISIT
                                    ,REMARK = @REMARK
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_UPLOAD = @IS_UPLOAD
                                    ,IS_DELETED = @IS_DELETED
                                WHERE (VISIT_NO = @VISIT_NO) AND (PROSPECT_ID = @PROSPECT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_VISIT_TMP
                                (
                                    PROSPECT_ID
                                    ,VISIT_NO
                                    ,OBJECTIVE_OF_VISIT
                                    ,DATE_VISIT_PLAN
                                    ,DATE_VISIT_ACTUAL
                                    ,JOINT_FIELD_WORK
                                    ,RESULT_OF_VISIT
                                    ,REMARK
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                    ,IS_DELETED
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@VISIT_NO
                                    ,@OBJECTIVE_OF_VISIT
                                    ,@DATE_VISIT_PLAN
                                    ,@DATE_VISIT_ACTUAL
                                    ,@JOINT_FIELD_WORK
                                    ,@RESULT_OF_VISIT
                                    ,@REMARK
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,@IS_UPLOAD
                                    ,@IS_DELETED
                                );
                            ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@VISIT_NO", DbType.Byte, Convert.ToByte(dr["VISIT_NO"]));
                        db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String, Convert.ToString(dr["OBJECTIVE_OF_VISIT"]));
                        db.AddInParameter(cmd, "@DATE_VISIT_PLAN", DbType.DateTime, Convert.ToDateTime(dr["DATE_VISIT_PLAN"]));
                        db.AddInParameter(cmd, "@DATE_VISIT_ACTUAL", DbType.DateTime, Convert.ToDateTime(dr["DATE_VISIT_ACTUAL"]));
                        db.AddInParameter(cmd, "@JOINT_FIELD_WORK", DbType.String, Convert.ToString(dr["JOINT_FIELD_WORK"]));
                        db.AddInParameter(cmd, "@RESULT_OF_VISIT", DbType.String, Convert.ToString(dr["RESULT_OF_VISIT"]));
                        db.AddInParameter(cmd, "@REMARK", DbType.String, Convert.ToString(dr["REMARK"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectVisit : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectProgress(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int result)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_PROGRESS_TMP
                                SET 
                                    PROSPECT_ID = @PROSPECT_ID
                                    ,PROGRESS_STATUS = @PROGRESS_STATUS
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                WHERE (PROSPECT_ID = @PROSPECT_ID) AND (PROGRESS_STATUS = @PROGRESS_STATUS);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_PROGRESS_TMP
                                (
                                    PROSPECT_ID
                                    ,PROGRESS_STATUS
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                    ,IS_UPLOAD
                                ) 
                                VALUES
                                (
                                    @PROSPECT_ID
                                    ,@PROGRESS_STATUS
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                    ,'1'
                                );
                            ");
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@PROGRESS_STATUS", DbType.String, Convert.ToString(dr["PROGRESS_STATUS"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);

                        if (db.ExecuteNonQuery(cmd) > 0) result++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectProgress : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadMaintenanceVisit(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int res)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand dbc = db.GetSqlStringCommand(@"
                            UPDATE TBL_MAINTENANCE_VISIT_SPAWEB SET IS_DOWNLOADED = '1'
                                    ,IS_DELETED = @IS_DELETED 
                                    ,[UPDATE_BY] = @UPDATE_BY
                                    ,[UPDATE_DATE] = @UPDATE_DATE
                            WHERE RECORD_ID = @RECORD_ID");
                        string recId = Convert.ToString(dr["RECORD_ID"]);
                        db.AddInParameter(dbc, "@RECORD_ID", DbType.String, recId);
                        db.AddInParameter(dbc, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));
                        db.AddInParameter(dbc, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(dbc, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.ExecuteNonQuery(dbc);

                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_MAINTENANCE_VISIT_TMP 
                                SET 
                                    [DATE_VISIT] = @DATE_VISIT
                                    ,[AO_CODE] = @AO_CODE
                                    ,[NO_ACCOUNT] = @NO_ACCOUNT
                                    ,[DEBTOR_NAME] = @DEBTOR_NAME
                                    ,[OBJECTIVE_OF_VISIT] = @OBJECTIVE_OF_VISIT
                                    ,[ENTRY_BY] = @ENTRY_BY
                                    ,[ENTRY_DATE] = @ENTRY_DATE
                                    ,[UPDATE_BY] = @UPDATE_BY
                                    ,[UPDATE_DATE] = @UPDATE_DATE
                                    ,[IS_DELETED] = @IS_DELETED
                                    ,[UPLOAD_BY] = @UPLOAD_BY
                                    ,[UPLOAD_DATE] = @UPLOAD_DATE
                                    ,[IS_UPLOAD] = @IS_UPLOAD

                                   ,[RECORD_ID] = @RECORD_ID
                                   ,[EXPIRY_DATE] = @EXPIRY_DATE
                                   ,[EXTEND_DATE] = @EXTEND_DATE
                                   ,[EXTEND_REASON] = @EXTEND_REASON
                                   ,[BATCH_ID] = @BATCH_ID
                                   ,[PROOF_CALL] = @PROOF_CALL
                                   ,[HASIL_PROOF_CALL] = @HASIL_PROOF_CALL
                                   ,[CIF] = @CIF
                                   ,[KONTAK_LOG] = @KONTAK_LOG
                                   ,[JENIS_KONTAK] = @JENIS_KONTAK
                                   ,[DATA_TYPE] = @DATA_TYPE
                                   ,[EXTEND_DATE_2] = @EXTEND_DATE_2
                                   ,[NIPTeller] = @NIPTeller
                                   ,[OLD_AO_CODE] = @OLD_AO_CODE

                                WHERE (RECORD_ID = @RECORD_ID) AND (VISIT_ID = @VISIT_ID);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_MAINTENANCE_VISIT_TMP
                                (
                                       [VISIT_ID]
                                      ,[PROSPECT_ID]
                                      ,[AO_CODE]
                                      ,[UNIT_CODE]
                                      ,[DATE_VISIT]
                                      ,[NO_ACCOUNT]
                                      ,[DEBTOR_NAME]
                                      ,[OBJECTIVE_OF_VISIT]
                                      ,[ENTRY_BY]
                                      ,[ENTRY_DATE]
                                      ,[UPDATE_BY]
                                      ,[UPDATE_DATE]
                                      ,[IS_DELETED]
                                      ,[UPLOAD_BY]
                                      ,[UPLOAD_DATE]
                                      ,[IS_UPLOAD]

                                       ,[RECORD_ID]
                                       ,[EXPIRY_DATE]
                                       ,[EXTEND_DATE]
                                       ,[EXTEND_REASON]
                                       ,[BATCH_ID]
                                       ,[PROOF_CALL]
                                       ,[HASIL_PROOF_CALL]
                                       ,[CIF]
                                       ,[KONTAK_LOG]
                                       ,[JENIS_KONTAK]
                                       ,[DATA_TYPE]
                                       ,[EXTEND_DATE_2]
                                       ,[NIPTeller]
                                       ,[OLD_AO_CODE]
                                ) 
                                VALUES
                                (
                                       @VISIT_ID
                                      ,@PROSPECT_ID
                                      ,@AO_CODE
                                      ,@UNIT_CODE
                                      ,@DATE_VISIT
                                      ,@NO_ACCOUNT
                                      ,@DEBTOR_NAME
                                      ,@OBJECTIVE_OF_VISIT
                                      ,@ENTRY_BY
                                      ,@ENTRY_DATE
                                      ,@UPDATE_BY
                                      ,@UPDATE_DATE
                                      ,@IS_DELETED
                                      ,@UPLOAD_BY
                                      ,@UPLOAD_DATE
                                      ,@IS_UPLOAD

                                       ,@RECORD_ID
                                       ,@EXPIRY_DATE
                                       ,@EXTEND_DATE
                                       ,@EXTEND_REASON
                                       ,@BATCH_ID
                                       ,@PROOF_CALL
                                       ,@HASIL_PROOF_CALL
                                       ,@CIF
                                       ,@KONTAK_LOG
                                       ,@JENIS_KONTAK
                                       ,@DATA_TYPE
                                       ,@EXTEND_DATE_2
                                       ,@NIPTeller
                                       ,@OLD_AO_CODE
                                );
                            ");

                        db.AddInParameter(cmd, "@VISIT_ID", DbType.String, Convert.ToString(dr["VISIT_ID"]));
                        db.AddInParameter(cmd, "@PROSPECT_ID", DbType.String, Convert.ToString(dr["PROSPECT_ID"]));
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                        db.AddInParameter(cmd, "@DATE_VISIT", DbType.DateTime, Convert.ToDateTime(dr["DATE_VISIT"]));
                        db.AddInParameter(cmd, "@NO_ACCOUNT", DbType.String, Convert.ToString(dr["NO_ACCOUNT"]));
                        db.AddInParameter(cmd, "@DEBTOR_NAME", DbType.String, Convert.ToString(dr["DEBTOR_NAME"]));
                        db.AddInParameter(cmd, "@OBJECTIVE_OF_VISIT", DbType.String, Convert.ToString(dr["OBJECTIVE_OF_VISIT"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");

                        db.AddInParameter(cmd, "@RECORD_ID", DbType.String, Convert.ToString(dr["RECORD_ID"]));
                        db.AddInParameter(cmd, "@EXPIRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["EXPIRY_DATE"]));
                        db.AddInParameter(cmd, "@EXTEND_DATE", DbType.DateTime, Convert.ToDateTime(dr["EXTEND_DATE"]));
                        db.AddInParameter(cmd, "@EXTEND_REASON", DbType.String, Convert.ToString(dr["EXTEND_REASON"]));
                        db.AddInParameter(cmd, "@BATCH_ID", DbType.String, Convert.ToString(dr["BATCH_ID"]));
                        db.AddInParameter(cmd, "@PROOF_CALL", DbType.String, Convert.ToString(dr["PROOF_CALL"]));
                        db.AddInParameter(cmd, "@HASIL_PROOF_CALL", DbType.String, Convert.ToString(dr["HASIL_PROOF_CALL"]));
                        db.AddInParameter(cmd, "@CIF", DbType.String, Convert.ToString(dr["CIF"]));
                        db.AddInParameter(cmd, "@KONTAK_LOG", DbType.String, Convert.ToString(dr["KONTAK_LOG"]));
                        db.AddInParameter(cmd, "@JENIS_KONTAK", DbType.String, Convert.ToString(dr["JENIS_KONTAK"]));
                        db.AddInParameter(cmd, "@DATA_TYPE", DbType.String, Convert.ToString(dr["DATA_TYPE"]));
                        db.AddInParameter(cmd, "@EXTEND_DATE_2", DbType.DateTime, Convert.ToDateTime(dr["EXTEND_DATE_2"]));
                        db.AddInParameter(cmd, "@NIPTeller", DbType.String, Convert.ToString(dr["NIPTeller"]));
                        db.AddInParameter(cmd, "@OLD_AO_CODE", DbType.String, Convert.ToString(dr["OLD_AO_CODE"]));
                        if (db.ExecuteNonQuery(cmd) > 0) res++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadMaintenanceVisit : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadSoTargetPlan(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int res)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_SO_TARGET_PLAN_TMP 
                                SET [TARGET_AMOUNT] = @TARGET_AMOUNT
                                    ,[NOA] = @NOA
                                    ,[TARGET_AMOUNT_REAL] = @TARGET_AMOUNT_REAL
                                    ,[NOA_REAL] = @NOA_REAL
                                    ,[ENTRY_BY] = LEFT(@ENTRY_BY, 9)
                                    ,[ENTRY_DATE] = @ENTRY_DATE
                                    ,[UPDATE_BY] = LEFT(@UPDATE_BY, 9)
                                    ,[UPDATE_DATE] = @UPDATE_DATE
                                    ,[IS_DELETED] = @IS_DELETED
                                    ,[UPLOAD_BY] = @UPLOAD_BY
                                    ,[UPLOAD_DATE] = @UPLOAD_DATE
                                    ,[IS_UPLOAD] = @IS_UPLOAD
                                WHERE (TARGET_YEAR = @TARGET_YEAR) AND (TARGET_MONTH = @TARGET_MONTH) AND (AO_CODE = @AO_CODE) AND (UNIT_CODE = @UNIT_CODE);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_SO_TARGET_PLAN_TMP 
                                (
                                       [TARGET_YEAR]
                                      ,[TARGET_MONTH]
                                      ,[AO_CODE]
                                      ,[UNIT_CODE]
                                      ,[TARGET_AMOUNT]
                                      ,[NOA]
                                      ,[TARGET_AMOUNT_REAL]
                                      ,[NOA_REAL]
                                      ,[ENTRY_BY]
                                      ,[ENTRY_DATE]
                                      ,[UPDATE_BY]
                                      ,[UPDATE_DATE]
                                      ,[IS_DELETED]
                                      ,[UPLOAD_BY]
                                      ,[UPLOAD_DATE]
                                      ,[IS_UPLOAD]
                                ) 
                                VALUES
                                (
                                       LEFT(@TARGET_YEAR,4)
                                      ,LEFT(@TARGET_MONTH,2)
                                      ,@AO_CODE
                                      ,@UNIT_CODE
                                      ,@TARGET_AMOUNT
                                      ,@NOA
                                      ,@TARGET_AMOUNT_REAL
                                      ,@NOA_REAL
                                      ,LEFT(@ENTRY_BY, 9)
                                      ,@ENTRY_DATE
                                      ,LEFT(@UPDATE_BY, 9)
                                      ,@UPDATE_DATE
                                      ,@IS_DELETED
                                      ,@UPLOAD_BY
                                      ,@UPLOAD_DATE
                                      ,@IS_UPLOAD
                                );
                            ");
                        db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, Convert.ToString(dr["TARGET_YEAR"]));
                        db.AddInParameter(cmd, "@TARGET_MONTH", DbType.String, Convert.ToString(dr["TARGET_MONTH"]));
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(dr["AO_CODE"]));
                        db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, Convert.ToString(dr["UNIT_CODE"]));
                        db.AddInParameter(cmd, "@TARGET_AMOUNT", DbType.Double, Convert.ToDouble(dr["TARGET_AMOUNT"]));
                        db.AddInParameter(cmd, "@NOA", DbType.Int32, Convert.ToString(dr["NOA"]));
                        db.AddInParameter(cmd, "@TARGET_AMOUNT_REAL", DbType.Double, Convert.ToDouble(dr["TARGET_AMOUNT_REAL"]));
                        db.AddInParameter(cmd, "@NOA_REAL", DbType.Int32, Convert.ToInt32(dr["NOA_REAL"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@IS_DELETED", DbType.String, Convert.ToString(dr["IS_DELETED"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");

                        if (db.ExecuteNonQuery(cmd) > 0) res++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadSoTargetPlan : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadProspectRefferal(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int res)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);

                try
                {
                    foreach (DataRow dr in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_PROSPECT_REFFERAL 
                                SET IS_DOWNLOADED = '0', IS_ASSIGNED = '0' 
                                WHERE REFFERAL_ID=@REFFERAL_ID;
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_PROSPECT_REFFERAL
                                (
                                    REFFERAL_ID
                                    ,FULL_NAME
                                    ,JENIS_USAHA
                                    ,OFFICE_PHONE
                                    ,RENCANA_PLAFON
                                    ,ADDRESS_BUSINESS
                                    ,UNIT_CODE_SOURCE
                                    ,UNIT_CODE_DEST
                                    ,AO_CODE_ORIG
                                    ,ENTRY_BY
                                    ,ENTRY_DATE
                                    ,UPDATE_BY
                                    ,UPDATE_DATE
                                    ,UPLOAD_BY
                                    ,UPLOAD_DATE
                                ) 
                                VALUES
                                (
                                    @REFFERAL_ID
                                    ,@FULL_NAME
                                    ,@JENIS_USAHA
                                    ,@OFFICE_PHONE
                                    ,@RENCANA_PLAFON
                                    ,LEFT(@ADDRESS_BUSINESS,255)
                                    ,@UNIT_CODE_SOURCE
                                    ,@UNIT_CODE_DEST
                                    ,@AO_CODE_ORIG
                                    ,@ENTRY_BY
                                    ,@ENTRY_DATE
                                    ,@UPDATE_BY
                                    ,@UPDATE_DATE
                                    ,@UPLOAD_BY
                                    ,@UPLOAD_DATE
                                );
                            ");
                        db.AddInParameter(cmd, "@REFFERAL_ID", DbType.String, Convert.ToString(dr["REFFERAL_ID"]));
                        db.AddInParameter(cmd, "@FULL_NAME", DbType.String, Convert.ToString(dr["FULL_NAME"]));
                        db.AddInParameter(cmd, "@JENIS_USAHA", DbType.String, Convert.ToString(dr["JENIS_USAHA"]));
                        db.AddInParameter(cmd, "@OFFICE_PHONE", DbType.String, Convert.ToString(dr["OFFICE_PHONE"]));
                        db.AddInParameter(cmd, "@RENCANA_PLAFON", DbType.Double, Convert.ToString(dr["RENCANA_PLAFON"]));
                        db.AddInParameter(cmd, "@ADDRESS_BUSINESS", DbType.String, Convert.ToString(dr["ADDRESS_BUSINESS"]));
                        db.AddInParameter(cmd, "@UNIT_CODE_SOURCE", DbType.String, Convert.ToString(dr["UNIT_CODE_SOURCE"]));
                        db.AddInParameter(cmd, "@UNIT_CODE_DEST", DbType.String, Convert.ToString(dr["UNIT_CODE_DEST"]));
                        db.AddInParameter(cmd, "@AO_CODE_ORIG", DbType.String, Convert.ToString(dr["AO_CODE_ORIG"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(dr["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToString(dr["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(dr["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToString(dr["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);

                        if (db.ExecuteNonQuery(cmd) > 0) res++;
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadProspectRefferal : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public string UploadUserZip(byte[] data, DateTime uploadDate, string loginUser, string loginPass, ref int res)
        {
            if (IsValidUser(loginUser, loginPass))
            {
                List<string> ao_code = new List<string>();
                Database db = DBConn.CreateDatabase();
                DataSet MyDataSet = TransformIntoDataSet(data);
                try
                {
                    foreach (DataRow theRow in MyDataSet.Tables[0].Rows)
                    {
                        DbCommand cmd = db.GetSqlStringCommand(@"
                                UPDATE TBL_MS_USER_ZIP_TMP 
                                SET AO_CODE = @AO_CODE
                                    ,ZIP_CODE = @ZIP_CODE
                                    ,KODYA_KAB = @KODYA_KAB
                                    ,KECAMATAN = @KECAMATAN
                                    ,KELURAHAN = @KELURAHAN
                                    ,ENTRY_BY = @ENTRY_BY
                                    ,ENTRY_DATE = @ENTRY_DATE
                                    ,UPDATE_BY = @UPDATE_BY
                                    ,UPDATE_DATE = @UPDATE_DATE
                                    ,UPLOAD_BY = @UPLOAD_BY
                                    ,UPLOAD_DATE = @UPLOAD_DATE
                                    ,IS_UPLOAD = @IS_UPLOAD
                                WHERE (AO_CODE = @AO_CODE AND ZIP_CODE = @ZIP_CODE AND KODYA_KAB = @KODYA_KAB AND KECAMATAN = @KECAMATAN AND KELURAHAN = @KELURAHAN);
                                IF @@ROWCOUNT = 0
                                INSERT INTO TBL_MS_USER_ZIP_TMP 
                                (
                                    AO_CODE,
                                    ZIP_CODE,
                                    KODYA_KAB,
                                    KECAMATAN,
                                    KELURAHAN,
                                    ENTRY_BY,
                                    ENTRY_DATE,
                                    UPDATE_BY,
                                    UPDATE_DATE,
                                    UPLOAD_BY,
                                    UPLOAD_DATE,
                                    IS_UPLOAD
                                ) 
                                VALUES
                                (
                                    @AO_CODE,
                                    @ZIP_CODE,
                                    @KODYA_KAB,
                                    @KECAMATAN,
                                    @KELURAHAN,
                                    @ENTRY_BY,
                                    @ENTRY_DATE,
                                    @UPDATE_BY,
                                    @UPDATE_DATE,
                                    @UPLOAD_BY,
                                    @UPLOAD_DATE,
                                    @IS_UPLOAD
                                );
                            ");
                        db.AddInParameter(cmd, "@AO_CODE", DbType.String, Convert.ToString(theRow["AO_CODE"]));
                        db.AddInParameter(cmd, "@ZIP_CODE", DbType.String, Convert.ToString(theRow["ZIP_CODE"]));
                        db.AddInParameter(cmd, "@KODYA_KAB", DbType.String, Convert.ToString(theRow["KODYA_KAB"]));
                        db.AddInParameter(cmd, "@KECAMATAN", DbType.String, Convert.ToString(theRow["KECAMATAN"]));
                        db.AddInParameter(cmd, "@KELURAHAN", DbType.String, Convert.ToString(theRow["KELURAHAN"]));
                        db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, Convert.ToString(theRow["ENTRY_BY"]));
                        db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, Convert.ToDateTime(theRow["ENTRY_DATE"]));
                        db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, Convert.ToString(theRow["UPDATE_BY"]));
                        db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, Convert.ToDateTime(theRow["UPDATE_DATE"]));
                        db.AddInParameter(cmd, "@UPLOAD_BY", DbType.String, loginUser);
                        db.AddInParameter(cmd, "@UPLOAD_DATE", DbType.DateTime, uploadDate);
                        db.AddInParameter(cmd, "@IS_UPLOAD", DbType.String, "1");

                        if (db.ExecuteNonQuery(cmd) > 0)
                        {
                            res++;
                            if (!ao_code.Contains(Convert.ToString(theRow["AO_CODE"])))
                            {
                                ao_code.Add(Convert.ToString(theRow["AO_CODE"]));
                            }
                        }
                    }
                    if (res == MyDataSet.Tables[0].Rows.Count)
                    {
                        foreach (string dr in ao_code)
                        {
                            DbCommand cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_MS_USER_ZIP WHERE AO_CODE = @AO_CODE");
                            db.AddInParameter(cmd, "@AO_CODE", DbType.String, dr);
                            db.ExecuteNonQuery(cmd);
                        }
                    }
                }
                catch (Exception ex)
                {
                    CreateErrorLog("enh2 UploadUserZip : " + ex.Message.ToString(), true);
                    return ex.Message;
                }
            }
            else
            {
                return "Failed User";
            }
            return "";
        }

        [WebMethod]
        public int DeleteUserZip(string ao_code, string loginUser, string loginPass)
        {
            int result = 0;
            if (IsValidUser(loginUser, loginPass))
            {
                Database db = DBConn.CreateDatabase();
                try
                {
                    DbCommand cmd = db.GetSqlStringCommand(@"DELETE FROM TBL_MS_USER_ZIP_TMP WHERE AO_CODE = @AO_CODE;");
                    db.AddInParameter(cmd, "@AO_CODE", DbType.String, ao_code);

                    if (db.ExecuteNonQuery(cmd) > 0) result++;
                }
                catch { return result; }
            }
            else return result;
            return result;
        }

        [WebMethod]
        public byte[] GetUserZip(string ao_code, string nip, string pass, DateTime updateDate)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_USER_ZIP (NOLOCK) WHERE AO_CODE = @AO_CODE AND UPDATE_DATE > @UPDATE_DATE");
                db.AddInParameter(cmd, "@AO_CODE", DbType.String, ao_code);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, updateDate);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public byte[] GetSoPlan(string nip, string pass, string unit_code, DateTime updateDate)
        {
            if (IsValidUser(nip, pass))
            {
                Database db = DBConn.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_SO_TARGET_PLAN (NOLOCK) WHERE UNIT_CODE = @UNIT_CODE AND UPDATE_DATE > @UPDATE_DATE");
                db.AddInParameter(cmd, "@UNIT_CODE", DbType.String, unit_code);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, updateDate);

                byte[] bite = TransformIntoBytes(db.ExecuteDataSet(cmd));
                return bite;
            }
            return null;
        }

        [WebMethod]
        public void UpdateLastActivity(string NIP, string IPAddress)
        {
            try
            {
                Database db = DBConn.CreateDatabase();
                DbCommand cmd;
                UserInfo info = GetLoginInfo(NIP, AppCode);
                info.IsLogin = true;
                info.LoginAddr = IPAddress;
                info.LoginDate = DateTime.Now;

                //update user
                cmd = db.GetStoredProcCommand("USER_UPDATE");
                db.AddInParameter(cmd, "@USER_ID", DbType.Int32, info.Id);
                db.AddInParameter(cmd, "@APP_ID", DbType.String, AppCode);
                db.AddInParameter(cmd, "@IS_LOGIN", DbType.String, "1");
                db.AddInParameter(cmd, "@LOGIN_ADDRESS", DbType.String, info.LoginAddr);

                db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                CreateErrorLog("enh2 UpdateLastActivity : " + ex.Message.ToString(), true);
            }
        }

        void CreateErrorLog(string text, bool isnewline)
        {
            try
            {
                string appPath = Server.MapPath("~/AppLog");
                if (appPath.Substring(appPath.Length - 1, 1) == "\\")
                {
                    appPath = appPath.Substring(0, appPath.Length - 1);
                }

                if (File.Exists(String.Format("{0}\\Log.txt", appPath)))
                {
                    using (StreamWriter file = new StreamWriter(String.Format("{0}\\Log.txt", appPath), true))
                    {
                        if (!isnewline) file.WriteLine();
                        file.WriteLine(String.Format("{0}", text));
                    }
                }
                else
                {
                    File.WriteAllText(String.Format("{0}\\Log.txt", appPath), text);
                }
            }
            catch (Exception) { }
        }
    }
}